The WordPress team has taken up the issue of performance optimization pretty seriously, and I look forward to the fruits of their efforts, but I’m also casting a critical eye on my own code. Thanks to caching and a hugely optimized query architecture, Scriblio is now performing better than ever, and I’m now looking at the next tier of problems.

First among them is a WordPress query that runs to find which posts have pingbacks or trackbacks waiting to be processed. As it was originally written, it looked like this:

$trackbacks = $wpdb->get_results("
	 SELECT ID 
	 FROM $wpdb->posts 
	 WHERE CHAR_LENGTH(TRIM(to_ping)) > 7 
	 AND post_status = 'publish'
	");

The problem is that the query requires a full table scan, and it can’t be optimized simply by creating an index on to_ping because CHAR_LENGTH(TRIM(to_ping)) > 7 requires inspecting every row and applying the functions before the result can be tested. As written, the query could take as long as 15 minutes to execute on a busy table of 350,000 rows.

Once I read through the code and determined that it’s very unlikely that cruft would be left in to_ping, or that any that was there would get removed once processed through the ping functions, I decided to try the query below. (Hat tip also to Andy Skelton for sanity checking my reading of the code.)

$trackbacks = $wpdb->get_results("
	 SELECT ID 
	 FROM $wpdb->posts 
	 WHERE to_ping <> '' 
	 AND post_status = 'publish'
	");

A trac ticket has been created and patch added, but despite the optimized query, a table as large as I’m working with probably needs an index to keep things running smoothly. So here’s what I’ve got:

KEY ping_status ( to_ping(1), post_status )

I’ve not yet confirmed that this is the most optimal index, but queries now take a fraction of a second to complete.

Posted January 23, 2008 by Casey Bisson
Categories: Technology. Tags: fixed, mysql, optimization, query, query optimization, wordpress.

No Comments

No comments yet.

Comments RSS TrackBack Identifier URI

Leave a comment

User contributed tags for this post: