Queries not being cached

[sivel]

I was recently playing with porting over an object cache to XCache, and after finishing the code I decided to log all mysql queries to check that it was working properly. What I found was a number of queries including post-tag and post-format queries that were being fired and not cached.

I have since tried Mark Jaquith's APC and Ryan Boren's Memcached object caches, as well as getting a list of queries run with an object cache not enabled.

I briefly talked to Nacin who looked at the list of queries running with the object cache enabled, who said they shouldn't be running, and that there must be something broken in trunk.

The set up is a brand new trunk install, without plugins, modifications of any type and running twenty ten.

Here is the list of queries that are running without the object cache:

80 Query	SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
80 Query	SELECT option_value FROM wp_options WHERE option_name = 'rewrite_rules' LIMIT 1
80 Query	SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY wp_posts.post_date DESC LIMIT 0, 10
80 Query	SELECT FOUND_ROWS()
80 Query	SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (1) ORDER BY t.name ASC
80 Query	SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (1)
80 Query	SELECT option_value FROM wp_options WHERE option_name = 'theme_mods_twentyten' LIMIT 1
80 Query	SELECT option_value FROM wp_options WHERE option_name = 'mods_Twenty Ten' LIMIT 1
80 Query	SELECT * FROM wp_posts  WHERE (post_type = 'page' AND post_status = 'publish')     ORDER BY menu_order, post_title ASC
80 Query	SELECT * FROM wp_users WHERE ID = 1 LIMIT 1
80 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (1)
80 Query	SELECT t.name FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (1)
80 Query	SELECT t.name FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (1)
80 Query	SELECT t.name FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (1)
80 Query	SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY wp_posts.post_date DESC LIMIT 0, 5
80 Query	SELECT FOUND_ROWS()
80 Query	SELECT * FROM wp_comments  WHERE comment_approved = '1' ORDER BY comment_date_gmt DESC LIMIT 5
80 Query	SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM wp_posts  WHERE post_type = 'post' AND post_status = 'publish' GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC
80 Query	SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') AND tt.count > 0 ORDER BY t.name ASC

Here is a list of queries that continue to run with the object cache enabled:

81 Query	SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY wp_posts.post_date DESC LIMIT 0, 10
81 Query	SELECT FOUND_ROWS()
81 Query	SELECT t.name FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (1)
81 Query	SELECT t.name FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (1)
81 Query	SELECT t.name FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (1)
81 Query	SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_tag') AND tr.object_id IN (1) ORDER BY t.name ASC
81 Query	SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_tag') AND tr.object_id IN (1) ORDER BY t.name ASC

I haven't had the time to test on 3.0.1 and not sure if that is really even needed. If it is let me know and I can run these tests there as well.

[nacin]

Those extra taxonomy queries should definitely be covered by the slurp.

So, the extra post_format queries are from wp_get_object_terms(), when $fields = names. The problem is, we're not accounting for the fact that we've already slurped $fields = all, at which point the cache should be skipped. wp_get_object_terms() doesn't fetch from the the cache internally.

We're calling $fields = names in get_post_format(). We should correct that.

Not sure where the post_tag ones are coming from.

[nacin]

Found #15400 while inspecting our queries.

[nacin]

(In [16319]) Use term cache in get_post_format() to save a boatload of queries. see #15396.

[nacin]

This query was caused by post_format being assigned to attachments as well, which then fired when the gallery shortcode called get_children.

SELECT t.*, tt.*, tr.object_id FROM wp31_terms AS t INNER JOIN wp31_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp31_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (525, 526, 527, 528, 529, 530, 531, 532, 533, 534, 535, 536) ORDER BY t.name ASC

[automattor]

(In [16322]) Only register post_format taxonomy to posts. see #15396, #14746.

[sivel]

Something has changed for the worst since this bug was reported.

Here are the queries running with an object cache at r16318:

Array
(
    [0] => Array
        (
            [0] =>  SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY wp_posts.post_date DESC LIMIT 0, 10
            [1] => 0.00077295303344727
            [2] => require, wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
        )
 
    [1] => Array
        (
            [0] => SELECT FOUND_ROWS()
            [1] => 0.00038599967956543
            [2] => require, wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
        )
 
    [2] => Array
        (
            [0] => SELECT t.name FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (1)  
            [1] => 0.00032305717468262
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, get_post_format, wp_get_object_terms
        )
 
    [3] => Array
        (
            [0] => SELECT t.name FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (1)  
            [1] => 0.00015401840209961
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, get_post_format, wp_get_object_terms
        )
 
    [4] => Array
        (
            [0] => SELECT t.name FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (1)  
            [1] => 0.00014209747314453
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, post_class, get_post_class, get_post_format, wp_get_object_terms
        )
 
    [5] => Array
        (
            [0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_tag') AND tr.object_id IN (1) ORDER BY t.name ASC
            [1] => 0.0010671615600586
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, post_class, get_post_class, get_the_tags, get_the_terms, wp_get_object_terms
        )
 
    [6] => Array
        (
            [0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_tag') AND tr.object_id IN (1) ORDER BY t.name ASC
            [1] => 0.001255989074707
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, get_the_tag_list, get_the_term_list, get_the_terms, wp_get_object_terms
        )
 
)

Here are the queries that are running with an object cache at r16472:

Array
(
    [0] => Array
        (
            [0] =>  SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY wp_posts.post_date DESC LIMIT 0, 10
            [1] => 0.000823974609375
            [2] => require, wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
        )
 
    [1] => Array
        (
            [0] => SELECT FOUND_ROWS()
            [1] => 0.00020313262939453
            [2] => require, wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
        )
 
    [2] => Array
        (
            [0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (1) ORDER BY t.name ASC
            [1] => 0.0025889873504639
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, in_category, has_term, is_object_in_term, wp_get_object_terms
        )
 
    [3] => Array
        (
            [0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (1) ORDER BY t.name ASC
            [1] => 0.00097179412841797
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, get_post_format, get_the_terms, wp_get_object_terms
        )
 
    [4] => Array
        (
            [0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (1) ORDER BY t.name ASC
            [1] => 0.001133918762207
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, in_category, has_term, is_object_in_term, wp_get_object_terms
        )
 
    [5] => Array
        (
            [0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (1) ORDER BY t.name ASC
            [1] => 0.0010449886322021
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, get_post_format, get_the_terms, wp_get_object_terms
        )
 
    [6] => Array
        (
            [0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_format') AND tr.object_id IN (1) ORDER BY t.name ASC
            [1] => 0.0010449886322021
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, post_class, get_post_class, get_post_format, get_the_terms, wp_get_object_terms
        )
 
    [7] => Array
        (
            [0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (1) ORDER BY t.name ASC
            [1] => 0.0012438297271729
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, post_class, get_post_class, get_the_category, get_the_terms, wp_get_object_terms
        )
 
    [8] => Array
        (
            [0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_tag') AND tr.object_id IN (1) ORDER BY t.name ASC
            [1] => 0.0013110637664795
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, post_class, get_post_class, get_the_tags, get_the_terms, wp_get_object_terms
        )
 
    [9] => Array
        (
            [0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (1) ORDER BY t.name ASC
            [1] => 0.0018959045410156
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, get_the_category, get_the_terms, wp_get_object_terms
        )
 
    [10] => Array
        (
            [0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (1) ORDER BY t.name ASC
            [1] => 0.0011169910430908
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, get_the_category_list, get_the_category, get_the_terms, wp_get_object_terms
        )
 
    [11] => Array
        (
            [0] => SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('post_tag') AND tr.object_id IN (1) ORDER BY t.name ASC
            [1] => 0.0011022090911865
            [2] => require, require_once, include, get_template_part, locate_template, load_template, require, get_the_tag_list, get_the_term_list, get_the_terms, wp_get_object_terms
        )
 
)

As you can see, previously we still had 7 queries running, now we are up to 12. At r16319, r16320, r16321, r16322, r16323 we were still at 7 queries, so I am not sure any of the commits that went in really helped. However, it looks like the additional queries that aren't being cached were added in [16332].

[ryan]

[16487] should clear a lot of those.