How to optimize this specific MySQL query which examines 2.84M rows and 29.49k InnoDB distinct pages

I am trying to figure out why one query need ~5 seconds to be completed and how to optimize it. Here is what information I gathered so far:

1. EXPLAIN statement for the query:

EXPLAIN EXTENDED
SELECT SUM(meta2.meta_value) FROM ****_posts as posts 
LEFT JOIN ****_postmeta AS meta ON posts.ID = meta.post_id 
LEFT JOIN ****_postmeta AS meta2 ON posts.ID = meta2.post_id 
WHERE
meta.meta_key       = '_customer_user' AND
meta.meta_value     = '15448' AND
posts.post_type     = 'shop_order' AND 
posts.post_status   IN ( 'wc-processing','wc-completed' ) AND
meta2.meta_key      = '_order_total';

+----+-------------+-------+--------+--------------------------+----------+---------+------------------------------+--------+----------+-------------+
| id | select_type | table | type   | possible_keys            | key      | key_len | ref                          | rows   | filtered | Extra       |
+----+-------------+-------+--------+--------------------------+----------+---------+------------------------------+--------+----------+-------------+
|  1 | SIMPLE      | meta2 | ref    | post_id,meta_key         | meta_key | 576     | const                        | 141630 |   100.00 | Using where |
|  1 | SIMPLE      | posts | eq_ref | PRIMARY,type_status_date | PRIMARY  | 8       | barberkl_wp821.meta2.post_id |      1 |   100.00 | Using where |
|  1 | SIMPLE      | meta  | ref    | post_id,meta_key         | post_id  | 8       | barberkl_wp821.meta2.post_id |     18 |   100.00 | Using where |
+----+-------------+-------+--------+--------------------------+----------+---------+------------------------------+--------+----------+-------------+

3 rows in set, 1 warning (0.06 sec)

```

1. pt-query-digest stats

Profile
Rank Query ID Response time Calls R/Call V/M Item
==== ================== ============= ===== ====== ===== ===============
1 0x5C7505FDD18B918C 4.7628 100.0% 1 4.7628 0.00 SELECT

Query 1: 0 QPS, 0x concurrency, ID 0x5C7505FDD18B918C at

The EAV pattern that is used in Wordpress' meta table leads to queries that need composite indexes. I suggest these three indexes:

****_postmeta 
  (meta_key, meta_value(30), post_id)       -- for meta
****_postmeta 
  (meta_key, post_id, meta_value(30))       -- for meta2
****_posts 
  (post_type, id, post_status)          -- for posts

Column meta_value is of type text so the whole column can't be put in the index, so we need to put only a few first characters (say (30)). You may also decide to change the type of the column to varchar(100) or something like that but that require more testing, first that no existing values are longer and second some kind of future checks so any Wordpress plugin that tries to store longer values doesn't fail (no idea how easy that is).

Also note that the LEFT joins can be replaced by INNER joins. The WHERE conditions make it equivalent.

StackExchange Database Administrators Q#177489, answer score: 5