How to optimize my Drupal database?
There are two easy ways to optimize the tables in your Drupal database.
The easiest way is to install the DB Maintenance module. Information on how to install a Drupal module is available in this Drupal tutorial.
After the module is installed and activated, you can access it from your Drupal admin area > Administer > Site configuration > DB maintenance. Select the tables which you wish to optimize and click Optimize now.
The other, slightly more complicated way, is to create a php script with the sql query. The code you should include in the php file should be similar to this:
<?php
$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 user, password and user_drpl1 to reflect your Drupal MySQL username, password and database.
This will optimize the tables accesslog, cache, comments, node, users and watchdog. Feel free to add or remove tables from the query.
Once you have inserted the code, save the file. For the purposes of this example, we’ll assume that the file is called optimize.php. Once the file is saved in your Drupal folder, execute it directly from a browser:
http://www.yourdomain.com/drupal/optimize.php
If you get a blank page without any errors, this means that the tables have been successfully optimized.
You can also set a cron job to execute the optimization script at regular intervals. The cron job you set should be similar to this:
php /home/user/public_html/drupal/optimize.phpMake sure you don’t set the cron to be executed too often. Once a week should be more than enough to keep your tables optimized.