Repair or optimize MySQL database on a shell
To fight fragmented databases, it is always a good idea to re-optimize your MySQL database especially if you have a lot of varying characters (VARCHAR).
The basic way is via a SQL query.
mysql> OPTIMIZE TABLE foo;
But easier is to do it on the shell. Here is the solution:
mysqlcheck -op database_name
The ‘o’ refers to optimize. And the ‘p’ is the option for password requirement. You’ll be prompted for the password.
If you’re logged in as user ‘john’ in your SSH, the MySQL username will be ‘john’. If that’s not what you want, use option ‘u’ like this:
mysqlcheck -op -u user_name database_name
And to optimize all databases:
mysqlcheck -op -u user_name –all-databases
Yuo can also repair and optimize at once:
mysqlcheck -orp -u
Also I advise you to use Innodb if you don't have Full text indexation on Myisam.
Query: SELECT min(dob) FROM $tableName
The hardest query performs a scan of all million rows.
InnoDB is better than MyISAM by ~30% with 4-16 threads, but MyISAM scales a bit better in this case.
InnoDB is better than Falcon by 2-3 times.