Knowledgebase
How to optimize MySQL databases via SSH
Optimizing your MySQL databases through SSH can be achieved with the command mysqlcheck. You also have to use the option -o. After you log in to your hosting account via SSH, to optimize a single MySQL database execute a command like this one:
mysqlcheck -o -u (username) -p (database)
Replace (username) with an actual username of a user that you have assigned to the particular database (it doesn't have to be the master user), and (database) with the full name of the database. For example, if the username is johnsmith and the name of the database mysite_database1, the command would look like this:
mysqlcheck -o -u johnsmith -p mysite_database1
After you execute the command you'll be prompted for the password of the user. Type it and press Enter on your keyboard. Once you do it, a list of the tables in the database will be displayed on the screen with OK next to each table (meaning that the table was optimized).
You can also optimize all databases that you have created with a single command. The command is the same, but in this case you have to use the username and password of the master user; the MySQL master user is added after you purchase your hosting account. You can set its password (and also change it) from the Databases section of the Pixie control panel (there's a Master Username link on the right). The following SSH command:
mysqlcheck -o -u (master username) -p --all-databases
will optimize all databases. Of course, you have to replace (master username) with the actual username of the master user
You can also optimize databases with phpMyAdmin. For more information on that check out the tutorial on optimizing MySQL databases through phpMyAdmin. Optimizing databases via SSH is very useful if you want to optimize many databases, or if the database(s) is a big one.