WordPress.org

Ready to get started?Download WordPress

Forums

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

  1. bobbyh
    Member
    Posted 5 years ago #

    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.

  2. _ck_
    Member
    Posted 5 years ago #

    What WordPress needs is a port of function bb_cache_users() from bbPress.

    I am simply dumbfounded that WordPress is up to version 2.7, three full years after 2.0, and still does not have a caching routine to stuff multiple users into the object cache with just two queries (one for wp_users and one for the meta) like bbPress does.

    (actually I've only checked the code up to 2.6.1 so maybe they finally put it into 2.7 but somehow I bet they didn't)

    With some clever workarounds to detect what kind of page it's on, a replacement multi-user caching routine could be done as a plugin instead of a core hack, via function get_userdata in pluggable.php - before it does a mysql query it could check the page type and gather the list of author id's or commenter ids and then cache them all with just two queries.

  3. _ck_
    Member
    Posted 5 years ago #

    I've now written a plugin to cache multiple users at once in WordPress.
    No template or core hacks required.

    It automatically tries to scan posts and comments before they are passed to the templates to pre-cache users with just two queries, regardless of the number of unique users in the posts/comments.

    The data is then available to WordPress and plugins via the regular get_userdata functions without extra mysql queries.

    On some blogs it should RADICALLY reduce the number of queries.

    Tested only up to WP 2.5.1 so let me know if it works in newer versions or not. Watch the query count that's sometimes hidden in the view source of your pages near the bottom.

    download: http://bbshowcase.org/plugins/wp-cache-users.zip

  4. rawalex
    Member
    Posted 5 years ago #

    WordPress has been all about shiny new features and admin layouts, and little about fixing the underlying issues of the software. WordPress is a great program if you have < 100 posts. As soon as you try to scale, you run across all sorts of problems, redundant queries,pages being built dynamically even though there are no changes on them for months at a time, etc.

    Again, wordpress works great to a certain extent, but it just doesn't work past a point (and I have found that point on more then one occassion)

  5. _ck_
    Member
    Posted 5 years ago #

    My plugin now has it's own page so you can always get the newest version here:
    http://wordpress.org/extend/plugins/wp-cache-users/

  6. stevegepa
    Member
    Posted 5 years ago #

    WordPress has been all about shiny new features and admin layouts, and little about fixing the underlying issues of the software. WordPress is a great program if you have < 100 posts. As soon as you try to scale, you run across all sorts of problems, redundant queries,pages being built dynamically even though there are no changes on them for months at a time, etc.

    Yes this has been my impression as well. Reading through the 2.7 and [upcoming] 2.8 feature list I can see little tempting me to upgrade from 2.6 I installed just a few weeks ago. I'm not interested in making things easier or prettier for me, but in being able to offer a more functional site for the visitors.

    I think maybe WordPress is possibly reaching the end of its life in the current incarnation. There seems to be little interest in adding real features or functionality. I see plenty of suggestions in these forums for useful additions, but even so, when asked to vote for what we want in the next release we only seem to be able to choose between mostly trivial rearrangements in the admin user interface.

    This is a shame as I've been fairly impressed so far in the first few weeks of using it.

    Steve

  7. rawalex
    Member
    Posted 5 years ago #

    Steve, I really think that the only way that wordpress really moved forward is for the developers to freeze out at 2.8, and start to work on 3.0 with an entirely new core that is built from the ground up for speed, efficiency, and most importantly to service the readers of blogs, not adding shiny tool for admins to play with.

    I don't see that happening, and I agree that wordpress might dead end at some point.

  8. jidanni
    Member
    Posted 5 years ago #

    Yes, they even bundled a RSS browser into it.
    They couldn't just let it be a optional plugin.

    The Linux kernel should bundle one too, else how could one keep
    up with the latest kernel events :-(.

    Also one drags the content to each article from the database, even if
    not going to show it.

    Also basic 304/Last modified is not implemented.

Topic Closed

This topic has been closed to new replies.

About this Topic