Mar 05

How to optimize MySQL database with a command

When you delete large portions for data from your MySQL table, the database becomes more fragmented. It is always a good idea to re-optimize your MySQL database especially if you have a lot of varying characters (VARCHAR). Here’s the command to optimize just one table:

OPTIMIZE TABLE foo

But I am looking for a more command line solution and here it is:

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

You can also use option ‘r’ to repair.

Note that the optimization only works for MyISAM, InnoDB, and ARCHIVE tables.

Possibly related:

  1. How to repair MySQL tables
  2. How to import and export MySQL database into an SQL file
  3. How to log slow MySQL queries
  4. MySQL.com compromised by SQL injection

This entry was posted on Thursday, March 5th, 2009 at 1:27 pm and has been tagged , , , , , , , . Comments RSS feed.

“How to optimize MySQL database with a command”
3 comments

  1. jer

    great idea for a cron script.

    Mar 05

  2. Thanks for very good article .its help to optimize my database.thanks again

    Dec 19

  3. thanks! helped allot :-)

    if you are reading this, you will probably be interesting in:
    http://www.mydigitallife.info/2008/07/29/mysql-database-performance-tuning-best-practices-video-tutorial/

    and this one too:
    http://blog.mysqltuner.com/

    :-)

    Feb 12

Leave your comment.

»
WordPress powered and Django inspired.
Love and elephants come after.
RSS: Posts and comments.