mysql slow queries and timeout in wordpress

Question

I am not an expert in sql. My wordpress started to return timeouts and respond really slow. when I started digging, I noticed that the slow_query log has a lot to tell me. unfortunately I have a lot of slow queries. for example:

# Time: 140425 17:03:29
# User@Host: geektime[geektime] @ localhost []
# Query_time: 7.024031  Lock_time: 0.000432 Rows_sent: 0  Rows_examined: 0
SET timestamp=1398434609;

SELECT wp_posts.*
FROM wp_posts
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
LEFT JOIN wp_postmeta AS order1 ON order1.post_id = wp_posts.ID
AND order1.meta_key = '_event_start_date'
LEFT JOIN wp_postmeta AS order2 ON order2.post_id = wp_posts.ID
AND order2.meta_key = '_event_start_time'
WHERE 1=1
  AND wp_posts.post_type = 'event'
  AND (wp_posts.post_status = 'publish'
       OR wp_posts.post_status = 'future'
       OR wp_posts.post_status = 'draft'
       OR wp_posts.post_status = 'pending')
  AND ((wp_postmeta.meta_key = '_event_start_date'
        AND CAST(wp_postmeta.meta_value AS CHAR) BETWEEN '2014-04-11' AND '2014-04-17')
       OR (mt1.meta_key = '_event_end_date'
           AND CAST(mt1.meta_value AS CHAR) BETWEEN '2014-04-11' AND '2014-04-17'))
GROUP BY wp_posts.ID
ORDER BY order1.meta_value,
         order2.meta_value ASC;

The columns post_id, meta_id and meta_key are indexed in wp_postmeta table. The columns ID, post_name, post_type, post_status, post_date,post_parent, post_author and guid are indexed in wp_posts table.

however, the columns ID and GUID are indexed twice, is it bad?

and there are 4 indexs with the same key_name: type_status_date, is it bad?

How could it be that I have 60K rows in wp_posts and 3M rows in wp_postmeta?

I know its a lot to ask but I really tried to understand from researching online.

thanks in advance.

Does this WordPress installation center around some specialized application like event scheduling? What plugins do you have in it? — O. Jones
unfortunately we are using a lot of plugins. but for above query, you are probably right, it probably has to do with the plugin Events Manager. — Meschiany
Separate indexing of columns in not always helpful. See this for a better way to index wp_metadata. — Rick James

Denis de Bernardy Denis de Bernardy · Accepted Answer · 2014-04-30T12:45:30

however, the columns ID and GUID are indexed twice, is it bad?

There are two different columns, so no, unless you're meaning that both have two indexes on them — in which case yes, it's bad and likely a bug in one of your theme or plugins (or a prior bug in WP itself).

and there are 4 indexs with the same key_name: type_status_date, is it bad?

Same as above: if you mean four identical indexes, it's either a theme or plugin or WP bug and you can safely drop the duplications.

How could it be that I have 60K rows in wp_posts and 3M rows in wp_postmeta?

Because the WP meta API sucks and enforces a database anti-pattern called the Entity Attribute Value (also known as EAV):

http://en.wikipedia.org/wiki/Entity-attribute-value_model

Cursory googling SO will yield plenty of threads that explain why it is a bad idea to store data in an EAV or equivalent (json, hstore, xml, whatever) if the stuff ever needs to appear in e.g. a where, join or order by clause.

You can see the inefficiencies first-hand in form of the slow query you highlighted. The query is joining the meta table four times, does so twice with a cast operator to boot — and it casts the value to char instead of date at that. Adding insult to injury, it then proceeds to order rows using values stored within it. It is a recipe for poor performance.

There is, sadly, little means of escaping the repulsive stench of this sewage, short of writing your own plugins that create proper tables to store, index and query the data you need in lieu of using the WP meta API, its wretched quoting madness, and the putrid SQL that results from using it.

One thing that you can do as temporary duct tape and WD-40 measure while you rewrite the plugins you're using from the ground up, is to toss callbacks on one or more of the filters you'll find in the giant mess of a class method that is WP_Query#get_posts(). For instance the posts_request filter, which holds the full and final SQL query, allows you to rewrite anything to your liking using regex-foo. It's no magic bullet: doing so will allow you to fix bugs such as integer values getting sorted lexicographically and such, as well as toss in very occasional query optimizations; little more.

Edit: Upon re-reading your query, methinks you're mostly in luck with respect to that last point. Your particular query features the following abomination:

INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
LEFT JOIN wp_postmeta AS order1 ON order1.post_id = wp_posts.ID
AND order1.meta_key = '_event_start_date'
LEFT JOIN wp_postmeta AS order2 ON order2.post_id = wp_posts.ID
AND order2.meta_key = '_event_start_time'

Two of those have _event_start_date in common, so you can factor it out:

SELECT wp_posts.*
FROM wp_posts
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
       AND wp_postmeta.meta_key = '_event_start_date'
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
       AND mt1.meta_key = '_event_end_date'
INNER JOIN wp_postmeta AS order2 ON order2.post_id = wp_posts.ID
AND order2.meta_key = '_event_start_time'
WHERE 1=1
  AND wp_posts.post_type = 'event'
  AND (wp_posts.post_status = 'publish'
       OR wp_posts.post_status = 'future'
       OR wp_posts.post_status = 'draft'
       OR wp_posts.post_status = 'pending')
  AND (CAST(wp_postmeta.meta_value AS CHAR) BETWEEN '2014-04-11' AND '2014-04-17'
       OR CAST(mt1.meta_value AS CHAR) BETWEEN '2014-04-11' AND '2014-04-17')
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value,
         order2.meta_value ASC;

mysql slow queries and timeout in wordpress

3 Answers