How to optimize my Drupal database?

There are easy ways to optimize the tables in your Drupal database.

The easiest way is to install the DB Maintenance module.

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.

Other way, slightly more complicated way, is to create a php script with the sql query. Then you can launch the script with a cronjob (if you've got access to it) The code you should include in the php file should be similar to this:

<?php

$db = mysql_connect('localhost','user','password');
if(!$db) echo "Error while connecting to the database";
mysql_select_db('drupal_db');
$result=mysql_query('OPTIMIZE TABLE cache,comments,node,users,watchdog;');

?>

You can also launch the SqlQuery if you've got access to PhpMyadmin:

OPTIMIZE TABLE cache,comments,node,users,watchdog;

You can optimize these tables every now and then to keep a good database performance.