Greg Palmer

If after upgrading to Wordpress 2.3 you’re getting database errors similar to
SELECT cat_ID AS ID, MAX(post_modified) AS last_mod FROM `posts` p LEFT JOIN `post2cat` pc ON…, it might be because you’re using the Google Sitemaps Plugin. (That link wasn’t the exact problem I was having, but I recognized the SQL statements as being similar.)

Easy fix - just download the new version (zip file).

For a while now I’ve found it odd that Drupal’s cron.php doesn’t optimize the more ephemeral tables within the database. (Maybe it does, but I’ve never seen it work). The cache, watchdog, and sessions tables in particular create a significant amount of overhead on a relatively busy site. So I wrote this daily maintenance script to keep the database size compact and clean out the overhead on the offending tables. Note that this is my first shell script ever, so it’s certainly ripe for criticism.

First, I created a .sql file to specify which tables I wanted optimized. I wanted to keep these commands separate from the shell script itself; no particular reason, it just felt cleaner to me. This file is called “dbmaint.sql”.

OPTIMIZE TABLE `bloginfo` , `cache` , `cache_filter` , `history` , `scheduler` , `sessions` , `url_alias` , `users` , `variable` , `watchdog`

Next, I created a shell script, which I called “dbmaint.cron”. The .cron extension isn’t necessary, but it’s a great way to understand at a glance that this script is run using a cron job. In that script, I wrote:

mysql --user=dbusernamehere –password=passwordhere databasenamehere < /var/www/pathgoeshere/scripts/dbmaint.sql

I used the full path to dbmaint.sql because the sh command didn’t like it otherwise. My final step was creating the cronjob, which used this command to execute the script:

sh /var/www/vhosts/pathgoeshere/scripts/dbmaint.cron

I’m not capturing the output because I want this happening on a regular basis and don’t need logging to confirm it. I can look at the database tables and know if they’ve been optimized in the past 24 hours or not just by experience in how much overhead should be created.

Anyway, hope that helps someone! I couldn’t find anything this simple out there, so I figured it was worth publishing my own.