Optimizing your MySQL database tables reorganizes table and index data, reducing space usage and improving I/O efficiency. This is particularly useful when phpMyAdmin encounters timeouts with large databases, as SSH provides a more reliable method for optimization.

How to Do It

  1. Step 1: Connect via SSH and Run the Optimization Command

    Use the following command to optimize all tables in your database:

    mysql -u username -ppassword -h hostname databasename -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "optimize table " $1 ";"}' | mysql -u username -ppassword -h hostname databasename

    Replace the placeholders with your actual database information:

    • username: Your database username
    • password: User's password
    • hostname: Active database hostname
    • databasename: Name of the database where you're running the command

    Optionally, capture the command's log by appending >> results.txt to review the results later.

  2. Step 2: Automate with a Cron Job (Optional)

    For monthly automated optimization, create a shell script with the following content:

    #!/bin/sh
/usr/bin/mysqlcheck -o -v -u USER -p PASSWORD -h MYSQL.EXAMPLE.COM DATABASENAME;

    Replace the uppercase placeholders (USER, PASSWORD, MYSQL.EXAMPLE.COM, and DATABASENAME) with your specific database credentials. Schedule this script as a monthly cron job to ensure routine maintenance and optimization of your MySQL tables.

  3. Step 3: Repair Tables Instead of Optimizing (If Needed)

    To repair a table instead of optimizing it, run the following command:

    mysql -u username -ppassword -h hostname databasename -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "repair table " $1 ";"}' | mysql -u username -ppassword -h hostname databasename

    Substitute the placeholders with your real database information.