MySQL – Query Not Using Cache

MySQLquery-cache

My customer is running MySQL 5.7.16 on Ubuntu16.04 as backend for WordPress site.
A lot of heavy, bad written SQLs.
I was able to improve situation significant by enabling MySQL cache, slow query log is almost empty now.
But – certain query doesn't use cache and I cannot understand why.
SQL uses two tables, which are pretty static, so it's not cache invalidation.
Any idea how I can debug that?

TIA, Vitaly

1) SHOW STATUS LIKE "qcache%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1700     |
| Qcache_free_memory      | 49555584 |
| Qcache_hits             | 17960465 |
| Qcache_inserts          | 6928571  |
| Qcache_lowmem_prunes    | 1850967  |
| Qcache_not_cached       | 924283   |
| Qcache_queries_in_cache | 13303    |
| Qcache_total_blocks     | 28441    |
+-------------------------+----------+

2)Example of SQL:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID 
FROM wp_posts  
  LEFT JOIN wp_postmeta 
    ON ( wp_posts.ID = wp_postmeta.post_id )  
  LEFT JOIN wp_postmeta AS mt1 
    ON (wp_posts.ID = mt1.post_id 
    AND mt1.meta_key = '_pagefrog_fbia_status' ) 
WHERE 1=1  
  AND (     ( wp_postmeta.meta_key = '_pagefrog_fbia_status' 
          AND wp_postmeta.meta_value = '1' )   
       OR     mt1.post_id IS NULL   
       OR   ( wp_postmeta.meta_key = '_pagefrog_fbia_status' 
          AND wp_postmeta.meta_value = '' ) ) 
  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, 10

Addenda

SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 83886080 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |    
+-----------------------------+----------+

Best Answer

  • When either table is modified, all entries in the QC are removed (pruned) from the QC for that table.
  • If the query has too big a resultset, it won't go into the QC. (Please provide SHOW VARIABLES LIKE 'query_cache*';

Do you have this on wp_postmeta: PRIMARY KEY(post_ID, meta_key) ? If you can switch to that, performance should improve when the QC is not used. If you have trouble, let's see SHOW CREATE TABLE wp_postmeta; some WP versions are really lame when it comes to optimizing that schema.

Do you need the GROUP BY? JOIN...GROUP BY often suffers from the explosion-implosion syndrome that makes such queries slow. There may be a workaround.

Will you be "paginating"? If so, use of OFFSET is inefficient.

Getting rid of SQL_CALC_FOUND_ROWS will speed up the query.

That is a confusing query, what is it trying to do?

Index

PRIMARY KEY (meta_id),         -- useless and slows things down
KEY post_id (post_id),         -- inadequate
KEY meta_key (meta_key(191))   -- prefixing is next to useless

Change to this

Change `meta_key` from VARCHAR(255) to VARCHAR(191)
PRIMARY KEY (post_id, meta_key)
no other indexes unless you ever search for meta_key without post_id

Notes:

  • You probably don't have very long keys, so changing to 191 may be safe. You could run SELECT MAX(CHAR_LENGTH(meta_key)) FROM .. to see what the current max is.
  • If you can't shrink to 191, then try to upgrade to 5.6.3 / 5.5.14, which allows you to increase the limit (with some effort), or 5.7.7, which allows it by default.
  • 'Composite' keys are often better than single-column keys, especially in this case.
  • Having the primary access pattern be the PK makes it faster.
  • These improvements may not help that particular contorted query, but it will help the table in other ways.

(I have been ranting about WP for years. I would be happy to advise them.)

Related Question