Broken blog = man obsessed

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 |
+----+-------------+----------+------+------------------------------+------------------+---------+-------------+------+----------------------------------------------+

Feel free to if you found this useful.

5 Comments

nice going.

btw, what’s the explain look like with IGNORE INDEX on .24 and .51?

Forcing it to ignore the PRIMARY key seems funky. In particular, note that the “correct” version is doing substantially more work (pulling almost 10 times as many rows, with a bigger key length)

Have you tried running “ANALYZE TABLE wp_posts” on both MySQL instances?

And what’s the GROUP BY doing in there? What happens when you remove it? It makes no sense to me to have a group by in there, since there’s no aggregates and it’s on a primary key.

Also: my fingers itch to figure out how to optimize the “wp_posts.id NOT IN (…)” out by replacing it with something like “post_category != 2”, “post_category != 2” or something like that… (adding an index on the column used, of course)

Nate, I added the EXPLAIN results with IGNORE INDEX in the query from v5.0.24a. Looks exactly like the v5.0.51a now.

Eric, yeah, you’re exactly right, a join would make A LOT more sense, but of course, but I’m guessing the “complexity” of developing a unified post query class with many potential joins led them to simplify the final query they send to the database to retrieve the posts. If your fingers are itching, definitely take a look at the wp-includes/query.php file to see how they do it.

scratch jones

Is there anything wrong?

Let’s review. In the first explain MySQL only had to analyze 600 or so rows. However the explain results
indicate both temporary and filesort.

According to MySQL,
“If you want to make your queries as fast as possible, you should look out for Extra values of Using filesort and Using temporary.”

On your laptop, MySQL has to examine over 5000 rows
however it does not require temporary or filesort.

With you “adjustments”,
you have been able to require the 5000 rows AND
temporary and filesort.
You have unoptimized your query to the fullest extent, nice job.

Scratch, yes, but in the unmodified query, MySQL got the “answer” to the query wrong. I don’t know about you, but I prefer unoptimized and correct to optimized and wrong.

Name

Email (optional)

Blog (optional)