There are two available options if you want to optimize the tables of your Drupal database.
- The easiest way to do this is to install the DB Maintenance module (You can find instructions on how to install Drupal modules in our knowledge base).
After you install and enable the module, you can use it by going to Administer > Site configuration > DB maintenance.
Just select the tables you want to optimize and click Optimize now. - The second, and a little more difficult, way is to create a php script file with an SQL query. The code for this php file should be similar as below:
$db = mysql_connect('localhost','user','password');
if(!$db) echo "cannot connect to the database";
mysql_select_db('user_drpl1');
$result=mysql_query('OPTIMIZE TABLE accesslog,cache,comments,node,users,watchdog;');
echo mysql_error();
?>Change the username, password and database with your MySQL credentials of your Drupal installation.
The above code will optimize the accesslog, cache, comments, node, users and watchdog tables. If you want you can add or remove tables from the query.
After you edit and place the code into the php file, save it. For this example, we suppose it's named optimize.php. As the file is saved in the Drupal installation folder, you can execute it directly from your browser:https://example.com/optimize.php
Finally after the execution you will see a blank page, this means that all the mentioned tables have been optimized successfully. If you see a "cannot connect to the database" error message, this means that some of your database credentials (username, password, database) are wrong. Correct it and try again.
