Hacking the Core: Is it worth it, to reduce # of MySQL queries in half?

I recently installed Jerome’s Query Diagnostics plugin, and was surprised to see that my front page was spawning 35 MySQL requests. Some were caused by plugins, but 20 of these were requests for user data, like so:

SELECT * FROM wp_users WHERE ID = 2417 LIMIT 1
SELECT meta_key, meta_value FROM wp_usermeta WHERE user_id = 2417

I have a multi-author WordPress 2.5.1 blog with 10 posts shown on each page. For each post, WordPress runs these two queries. I believe (although I have not checked) that these queries are run even if the author is the same. (Please don’t hate me if this isn’t true.)

These are tiny selects, but there is overhead associated with these calls. The function that spawns these queries is in /wp-inclues/query.php on line 1676:

$authordata = get_userdata($post->post_author);

You can hack this core file like so to reduce the number of queries:

if ( is_admin() ) {
$authordata = get_userdata($post->post_author);
}

Note: the admin needs these functions, and has fewer pageviews than a popualr blog, so I kept it.

Anyway, you will now lose the use of your author functions, like the_author_url() and the_author(). I also use the data stored in the usermeta table with the keys “description” and “yim” (Yahoo Instant Messenger). To restore this functionality with two queries (instead of 2 x the number of posts), I wrote the following code which can go into your functions.php or index.php templates…

*This code does the two database queries:*

global $posts;
foreach ($posts as $post) {$author_array[] = $post->post_author;}
$user_db_query = implode(",", $author_array);
$user_results = $wpdb->get_results("SELECT * FROM wp_users WHERE ID IN ($user_db_query)");
$usermeta_results = $wpdb->get_results("SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN ($user_db_query) AND meta_key IN ('description','yim')");

*This code separates the array $usermeta_results (which contains data on both “description” and “yim” into two separate arrays, $user_yim_array and $user_description_array*

for ($i=0; $i<sizeof($usermeta_results); $i++) {
if ($usermeta_results[$i]->meta_key == 'yim') {
$user_yim_array[] = $usermeta_results[$i];
} else {
$user_description_array[] = $usermeta_results[$i];
}
}

*This code sorts the arrays in the order of the posts (stored in $author_array) and then creates new arrays for each variable you want, e.g. the yim values or the display names*

for ($i=0; $i<sizeof($posts); $i++) {
$author_yims[] = usermeta_variable ($user_yim_array, $i);
$author_display_names[] = user_variable ('display_name',$i);
$author_descriptions[] = usermeta_variable ($user_description_array, $i);
$author_user_urls[] = user_variable ('user_url', $i);
}

*This code does the sorting used in the previous code block*

function usermeta_variable ($array_var, $list_order) {
global $author_array;
$author_id = $author_array[$list_order];

for ($i=0; $i<sizeof($array_var); $i++) {
if ($array_var[$i]->user_id == $author_id) {
return $array_var[$i]->meta_value;
}
}
}

function user_variable ($lookup, $list_order) {
global $author_array, $user_results;
$author_id = $author_array[$list_order];

for ($i=0; $i<sizeof($user_results); $i++) {
if ($user_results[$i]->ID == $author_id) {
return $user_results[$i]->$lookup;
}
}
}

Now, modify your Loop like so:

<?php $i = 0; ?>

<?php if (have_posts()) : ?>
<?php while (have_posts()) : the_post(); ?>
your normal non-author related theme functions like the_content();
<?php echo $author_descriptions[$i];?>
<?php echo $author_display_names[$i];?>
<?php echo $author_yims[$i];?>
<?php echo $author_yims[$i];?>
<?php echo $author_user_urls[$i];?>
<?php $i++; ?>
<?php endwhile; ?>
<?php endif; ?>

Well, this code works and it reduced the number of queries on my blog by 20 from 35 to 15. I then tweaked some plugin queries and got the number of queries down to 11. This took me a while to figure out, so I thought I’d share for the sake of anybody else who would like to lower their database load.

I would code a generalized patch for the WordPress core and submit it, but I am not confident enough in my PHP and MySQL haxor skills. But if you’re smarter than me, maybe you can figure out how to write better code suitable for a core patch that does the same thing? Specifically, rather than do two queries per post/user, do two queries for all the relevant wp_user and wp_usermeta data.