Databases are supposed to be these monolithic paragons of correctness and uniformity. So it’s a little unsettling to discover a database that returns grossly incorrect results. At least I wouldn’t expect WordPress to put version 5 of MySQL through its paces.

Here’s the scenario. Dreamhost, my webhost, is running MySQL Server v5.0.24a, released in August 2006. On my laptop I’m running MySQL Server v5.0.51a, released in January 2008. The difference between the two is a year and a half of bugfixes and performance improvements.

On each I’ve got a copy of the same database table, wp_posts, from my WordPress 2.5.1 install for Justinsomnia. And on each system I’ve got a copy of the giant SQL select statement generated by WordPress that returns the first 8 blog posts that should appear on my homepage. The unusual thing about this particular query is that it contains an insanely long NOT IN () list to filter out my neatlinks from the main column.

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* 
FROM wp_posts  
WHERE 1=1  
AND wp_posts.ID NOT IN ('876', '877', '878', '879', '880'...'5456', '5458', '5460', '5461', '5462', '5463') 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC 
LIMIT 0, 8

The problem is that running the same SQL statement on each server returns different results—and I wanted to figure out why.

So I prefixed the query with EXPLAIN, to see what each version of MySQL would tell me about how they were dealing with the same query. I expected the results to be the same. They turned out to be quite different.

MySQL 5.0.24a:
+----+-------------+----------+-------+--------------------------------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table    | type  | possible_keys                        | key     | key_len | ref  | rows | Extra                                        |
+----+-------------+----------+-------+--------------------------------------+---------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | wp_posts | range | PRIMARY,post_status,type_status_date | PRIMARY | 4       | NULL |  611 | Using where; Using temporary; Using filesort |
+----+-------------+----------+-------+--------------------------------------+---------+---------+------+------+----------------------------------------------+

MySQL 5.0.51a:
+----+-------------+----------+------+--------------------------------------+------------------+---------+-------------+------+-------------+
| id | select_type | table    | type | possible_keys                        | key              | key_len | ref         | rows | Extra       |
+----+-------------+----------+------+--------------------------------------+------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | wp_posts | ref  | PRIMARY,post_status,type_status_date | type_status_date | 25      | const,const | 5350 | Using where | 
+----+-------------+----------+------+--------------------------------------+------------------+---------+-------------+------+-------------+

As you scroll to the right, you can see that v5.0.24a chose the PRIMARY key to find the most recent posts, while v5.0.51a opted for type_status_date. I’m not quite sure what that means, except that v5.0.24a obviously made the wrong choice. Which gave me an idea for an interim solution (while I wait for Dreamhost to upgrade MySQL): index hints.

If I could force MySQL to ignore the PRIMARY key, maybe it would “fix” my blog. So I added IGNORE INDEX (PRIMARY) to the query that selects posts, and voila, my blog homepage started working again! Here’s the diff of the code change I made. Luckily I have WordPress “installed” via Subversion, so any changes I make to core files get carried over with each upgrade.

Index: wp-includes/query.php
===================================================================
--- wp-includes/query.php	(revision 7987)
+++ wp-includes/query.php	(working copy)
@@ -1424,7 +1424,7 @@
 		if ( !empty($limits) )
 			$found_rows = 'SQL_CALC_FOUND_ROWS';
 
-		$request = " SELECT $found_rows $distinct $fields FROM $wpdb->posts $join WHERE 1=1 $where $groupby $orderby $limits";
+		$request = " SELECT $found_rows $distinct $fields FROM $wpdb->posts IGNORE INDEX (PRIMARY) $join WHERE 1=1 $where $groupby $orderby $limits";
 		$this->request = apply_filters('posts_request', $request);
 
 		$this->posts = $wpdb->get_results($this->request);

Hopefully I haven’t inadvertently broken some other aspect of my blog. If anyone sees anything out of order, please leave a comment, mmmk?

Update: for Nate, here’s the EXPLAIN on 5.0.24a with the IGNORE INDEX:

+----+-------------+----------+------+------------------------------+------------------+---------+-------------+------+----------------------------------------------+
| id | select_type | table    | type | possible_keys                | key              | key_len | ref         | rows | Extra                                        |
+----+-------------+----------+------+------------------------------+------------------+---------+-------------+------+----------------------------------------------+
|  1 | SIMPLE      | wp_posts | ref  | post_status,type_status_date | type_status_date | 25      | const,const | 5351 | Using where; Using temporary; Using filesort |
+----+-------------+----------+------+------------------------------+------------------+---------+-------------+------+----------------------------------------------+