Optimize for WooCommerce: heavy shop_order post query ideas?

Hi. Thanks for all your work here, looks promising.

Before I half-blind start modding my production database, I thought I’d get in touch for some additional insight.

I ran pt-query-digest on our slowlog, and while there’s much to work, let’s try to get the top dog:

# Query 1: 0.00 QPS, 0.04x concurrency, ID 0x28AB87B871626673FCB0584780489FAC at byte 225925344
# This item is included in the report because it matches --limit.
# Scores: V/M = 4.68
# Time range: 2018-07-05 11:05:20 to 2021-11-29 06:41:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         47  162236
# Exec time     39 3843531s     10s    468s     24s     40s     11s     21s
# Lock time      2      8s    34us     6ms    51us    63us    50us    44us
# Rows sent      0 312.21k       0     212    1.97    7.70    5.68    0.99
# Rows examine  34 405.46G 603.47k   3.49M   2.56M   3.34M 487.83k   2.26M
# Query size    42  58.67M     373     383  379.19  381.65       5  363.48
# String:
# Databases    wp
# Hosts        localhost
# Users        wp
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
# Tables
#    SHOW TABLE STATUS FROM <code>wp</code> LIKE 'wp_posts'\G
#    SHOW CREATE TABLE <code>wp</code>.<code>wp_posts</code>\G
#    SHOW TABLE STATUS FROM <code>wp</code> LIKE 'wp_postmeta'\G
#    SHOW CREATE TABLE <code>wp</code>.<code>wp_postmeta</code>\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT posts.ID
                                FROM wp_posts AS posts
                                LEFT JOIN wp_postmeta AS meta on posts.ID = meta.post_id
                                WHERE meta.meta_key = '_customer_user'
                                AND   meta.meta_value = '3595'
                                AND   posts.post_type = 'shop_order'
                                AND   posts.post_status IN ( 'wc-pending','wc-processing','wc-on-hold','wc-completed','wc-cancelled','wc-refunded','wc-failed' )
                                ORDER BY posts.ID DESC

EXPLAIN says we definitely don’t have the necessary indexes in place thus far

MariaDB [wp]> EXPLAIN SELECT posts.ID                                 FROM wp_posts AS posts                                 LEFT JOIN wp_postmeta AS meta on posts.ID = meta.post_id                                 WHERE meta.meta_key = '_customer_user'                                 AND   meta.meta_value = '32493'                                 AND   posts.post_type = 'shop_order'                                 AND   posts.post_status IN ( 'wc-pending','wc-processing','wc-on-hold','wc-completed','wc-cancelled','wc-refunded','wc-failed' )                                 ORDER BY posts.ID DESC;
+------+-------------+-------+-------+----------------------------+------------------+---------+----------------------------+--------+------------------------------------------+
| id   | select_type | table | type  | possible_keys              | key              | key_len | ref                        | rows   | Extra                                    |
+------+-------------+-------+-------+----------------------------+------------------+---------+----------------------------+--------+------------------------------------------+
|    1 | SIMPLE      | posts | range | PRIMARY,type_status_date   | type_status_date | 164     | NULL                       | 108897 | Using where; Using index; Using filesort |
|    1 | SIMPLE      | meta  | ref   | post_id,meta_key,post_id_2 | post_id          | 8       | wp.posts.ID |      1 | Using where                              |
+------+-------------+-------+-------+----------------------------+------------------+---------+----------------------------+--------+------------------------------------------+
2 rows in set (0.002 sec)

Index states

MariaDB [wp]> SHOW INDEX FROM wp_postmeta;
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_postmeta |          0 | PRIMARY   |            1 | meta_id     | A         |     4943116 |     NULL | NULL   |      | BTREE      |         |               |
| wp_postmeta |          1 | post_id   |            1 | post_id     | A         |     4943116 |     NULL | NULL   |      | BTREE      |         |               |
| wp_postmeta |          1 | meta_key  |            1 | meta_key    | A         |       17344 |      191 | NULL   | YES  | BTREE      |         |               |
| wp_postmeta |          1 | post_id_2 |            1 | post_id     | A         |     4943116 |     NULL | NULL   |      | BTREE      |         |               |
| wp_postmeta |          1 | post_id_2 |            2 | meta_key    | A         |     4943116 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.000 sec)

MariaDB [wp]> SHOW INDEX FROM wp_posts;
+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_posts |          0 | PRIMARY          |            1 | ID           | A         |     4250786 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date |            1 | post_type    | A         |         118 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date |            2 | post_status  | A         |         274 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date |            3 | post_date    | A         |     4250786 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date |            4 | ID           | A         |     4250786 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_parent      |            1 | post_parent  | A         |       41674 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_author      |            1 | post_author  | A         |       51214 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_name        |            1 | post_name    | A         |     4250786 |      191 | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | bsearch_title    |            1 | post_title   | NULL      |        NULL |     NULL | NULL   |      | FULLTEXT   |         |               |
| wp_posts |          1 | bsearch_content  |            1 | post_content | NULL      |        NULL |     NULL | NULL   |      | FULLTEXT   |         |               |
+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.000 sec)

What are your thoughts on how to index for this? Does one of the plugin’s built-in strategies apply to this already, or need to think custom?

Willing to to pay for consultation time, let me know about options, thank you.

• This topic was modified 2 years, 8 months ago by lkraav.
• This topic was modified 2 years, 8 months ago by lkraav.
• This topic was modified 2 years, 8 months ago by lkraav.
• This topic was modified 2 years, 8 months ago by lkraav.