3

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)

2. 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 byte 0 ________
 This item is included in the report because it matches --limit.
 Scores: V/M = 0.00
 Time range: all events occurred at 2017-06-28 07:16:34
 Attribute    pct   total     min     max     avg     95%  stddev  median
 ============ === ======= ======= ======= ======= ======= ======= =======
 Count        100       1
 Exec time    100      5s      5s      5s      5s      5s       0      5s
 Lock time    100   173us   173us   173us   173us   173us       0   173us
 Rows sent    100       1       1       1       1       1       0       1
 Rows examine 100   2.84M   2.84M   2.84M   2.84M   2.84M       0   2.84M
 Rows affecte   0       0       0       0       0       0       0       0
 Bytes sent   100      77      77      77      77      77       0      77
 Merge passes   0       0       0       0       0       0       0       0
 Tmp tables     0       0       0       0       0       0       0       0
 Tmp disk tbl   0       0       0       0       0       0       0       0
 Tmp tbl size   0       0       0       0       0       0       0       0
 Query size   100     412     412     412     412     412       0     412
 InnoDB:
 IO r bytes     0       0       0       0       0       0       0       0
 IO r ops       0       0       0       0       0       0       0       0
 IO r wait      0       0       0       0       0       0       0       0
 pages distin 100  29.49k  29.49k  29.49k  29.49k  29.49k       0  29.49k
 queue wait     0       0       0       0       0       0       0       0
 rec lock wai   0       0       0       0       0       0       0       0

I am running Percona 5.6 and if needed I can provide the configuration. Please guide me where to start. Thanks!

Update (SHOW CREATE TABLE):

 mysql> SHOW CREATE TABLE wpn0_postmeta \G;
 *************************** 1. row ***************************
        Table: wpn0_postmeta
 Create Table: CREATE TABLE `wpn0_postmeta` (
   `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
   `meta_key` varchar(255) DEFAULT NULL,
   `meta_value` longtext,
   PRIMARY KEY (`meta_id`),
   KEY `post_id` (`post_id`),
   KEY `meta_key` (`meta_key`(191)),
   KEY `woo_index1` (`post_id`,`meta_key`,`meta_value`(8))
 ) ENGINE=InnoDB AUTO_INCREMENT=6681190 DEFAULT CHARSET=utf8
 1 row in set (0.00 sec)

 mysql> SHOW CREATE TABLE wpn0_posts \G;
 *************************** 1. row ***************************
        Table: wpn0_posts
 Create Table: CREATE TABLE `wpn0_posts` (
   `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
   `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `post_content` longtext NOT NULL,
   `post_title` text NOT NULL,
   `post_excerpt` text NOT NULL,
   `post_status` varchar(20) NOT NULL DEFAULT 'publish',
   `comment_status` varchar(20) NOT NULL DEFAULT 'open',
   `ping_status` varchar(20) NOT NULL DEFAULT 'open',
   `post_password` varchar(255) NOT NULL DEFAULT '',
   `post_name` varchar(200) NOT NULL DEFAULT '',
   `to_ping` text NOT NULL,
   `pinged` text NOT NULL,
   `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `post_content_filtered` longtext NOT NULL,
   `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
   `guid` varchar(255) NOT NULL DEFAULT '',
   `menu_order` int(11) NOT NULL DEFAULT '0',
   `post_type` varchar(20) NOT NULL DEFAULT 'post',
   `post_mime_type` varchar(100) NOT NULL DEFAULT '',
   `comment_count` bigint(20) NOT NULL DEFAULT '0',
   PRIMARY KEY (`ID`),
   KEY `post_name` (`post_name`(191)),
   KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
   KEY `post_parent` (`post_parent`),
   KEY `post_author` (`post_author`),
   KEY `woo_index2` (`post_type`,`post_status`,`ID`)
 ) ENGINE=InnoDB AUTO_INCREMENT=349189 DEFAULT CHARSET=utf8
 1 row in set (0.00 sec)
  • Can't you remove meta2 join and use meta.meta_key IN ('_customer_user','_order_total') ? – Prabhat G Jun 28 '17 at 12:34
  • 2
    Show us the table definitions, with SHOW CREATE TABLE tablename; for both tables. – ypercubeᵀᴹ Jun 28 '17 at 12:38
  • @ypercubeᵀᴹ I added the requested info. Also I created two indexes that optimized the query very much - woo_index2 and woo_index1. However, to me it seems like I am only patching around while the source of the issue is that this WHERE clause is checking so many rows that are longtext. Probably it will be better to use MATCH AGAINST. Thanks and I will simply use the indexes for now. – Daniel Kanchev Jun 28 '17 at 12:57
  • 1
    Ok, meta_value is text so that complicates the problem. – ypercubeᵀᴹ Jun 28 '17 at 13:03
  • 1
    @mustaccio this is a WordPress WooCommerce site with many plugins. I am not the developer of the query and I am simply trying to fix this particular query because I saw it too many times in the slow log. I will get in touch with the devs in order to provide some feedback. – Daniel Kanchev Jun 28 '17 at 13:17
5

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.

|improve this answer|||||
  • Thanks for that - I already added the indexes and this improved the situation very much :) – Daniel Kanchev Jun 28 '17 at 12:58
  • 1
    So what's the response time now? And what is the new plan, after the indexes? – ypercubeᵀᴹ Jun 28 '17 at 13:03
  • Once I created the indexes the same query is executed for ~0.6 seconds. For now this suits me and if I see more slow queries like this one I will try to optimize the query itself. – Daniel Kanchev Jun 28 '17 at 13:05
  • 1
    But you added different indexes than my suggestion. The order of the columns in the index matters. – ypercubeᵀᴹ Jun 28 '17 at 13:06
  • 1
    Yes it will decide but I'd remove them (the ones you added) either now or later. You can leave them for now - and see which ones MySQL decides to use! – ypercubeᵀᴹ Jun 28 '17 at 13:08
0

"Prefix" indexes are usually not worth using, especially when they are not the last part of a composite index.

My recommendations involve changing the post_meta schema in multiple was; see this, which includes the rationale for each step: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

CREATE TABLE wp_postmeta (
    post_id BIGINT UNSIGNED NOT NULL,
    meta_key VARCHAR(255) NOT NULL,
    meta_value LONGTEXT NOT NULL,
    PRIMARY KEY(post_id, meta_key),
    INDEX(meta_key)
    ) ENGINE=InnoDB;

INDEX(meta_key, post_id, meta_value(30)) is not as good as PRIMARY KEY(post_id, meta_key) because

  • Secondary keys first dig into the secondary BTree, then reach into the PRIMARY BTree; this avoids the first step.
  • Prefix indexing does not help much.

By having PRIMARY KEY(post_id, meta_key), all the rows you might need to scan are adjacent, thereby leading to very few blocks needing to be fetched.

Changing the postmeta table as above will speed up most WP queries that involve it. But the real problem with the SELECT is as follows...

The most important filtering (user=15448) is hiding behind a LEFT JOIN is a secondary table. The user_id needs to be in posts and indexed. Or at least remove LEFT so that the Optimizer might start with meta instead of posts. At that point ypercube's (meta_key, meta_value(30), post_id) would be somewhat useful. Alas, it won't be "covering" because of the unavoidable "prefix".

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.