How can I make this MySQL query (with a sub query) faster?

Question

I have the following query which gets order data, works fine but takes 13.63 seconds:

SELECT
( SELECT meta_value FROM `myprefix_postmeta` as postmeta WHERE postmeta.meta_key = '_sku' AND postmeta.post_id = woocommerce_order_itemmeta3.meta_value ) AS sku,
woocommerce_order_items.order_item_name AS title,
SUM( woocommerce_order_itemmeta.meta_value ) AS quantity,
SUM( woocommerce_order_itemmeta2.meta_value ) AS total
FROM `myprefix_woocommerce_order_items` AS woocommerce_order_items
LEFT JOIN `myprefix_posts` AS posts ON posts.ID = woocommerce_order_items.order_id
LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS woocommerce_order_itemmeta ON woocommerce_order_itemmeta.order_item_id = woocommerce_order_items.order_item_id
LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS woocommerce_order_itemmeta2 ON woocommerce_order_itemmeta2.order_item_id =   woocommerce_order_items.order_item_id
LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS woocommerce_order_itemmeta3 ON woocommerce_order_itemmeta3.order_item_id =   woocommerce_order_items.order_item_id
WHERE posts.post_type = 'shop_order'
AND posts.post_status IN( 'wc-completed', 'wc-processing', 'wc-on-hold' )
AND woocommerce_order_itemmeta.meta_key = '_qty'
AND woocommerce_order_itemmeta2.meta_key = '_line_total' 
AND woocommerce_order_itemmeta3.meta_key = '_product_id'
GROUP BY woocommerce_order_itemmeta3.meta_value
ORDER BY total DESC

I want to optimise this query to run faster.

Maybe it simply takes this long to return. However, I have tried removing the sub query in the above anyway resulting in the following:

SELECT
postmeta.meta_value AS sku,
woocommerce_order_items.order_item_name AS title,
SUM( woocommerce_order_itemmeta.meta_value ) AS quantity,
SUM( woocommerce_order_itemmeta2.meta_value ) AS total
FROM `myprefix_woocommerce_order_items` AS woocommerce_order_items
LEFT JOIN `myprefix_posts` AS posts ON posts.ID = woocommerce_order_items.order_id
LEFT JOIN `myprefix_postmeta` AS postmeta ON posts.ID = woocommerce_order_items.order_id
LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS woocommerce_order_itemmeta ON woocommerce_order_itemmeta.order_item_id = woocommerce_order_items.order_item_id
LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS woocommerce_order_itemmeta2 ON woocommerce_order_itemmeta2.order_item_id =   woocommerce_order_items.order_item_id
LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS woocommerce_order_itemmeta3 ON woocommerce_order_itemmeta3.order_item_id =   woocommerce_order_items.order_item_id
WHERE posts.post_type = 'shop_order'
AND posts.post_status IN( 'wc-completed', 'wc-processing', 'wc-on-hold' )
AND postmeta.meta_key = '_sku'
AND postmeta.post_id = woocommerce_order_itemmeta3.meta_value
AND woocommerce_order_itemmeta.meta_key = '_qty'
AND woocommerce_order_itemmeta2.meta_key = '_line_total' 
AND woocommerce_order_itemmeta3.meta_key = '_product_id'
GROUP BY woocommerce_order_itemmeta3.meta_value
ORDER BY total DESC

This is marginally faster by milliseconds, but is there any way to optimise this further?

I appreciate you may need to understand the data behind these tables, they are standard WordPress database tables & WooCommerce Database Schema, I can't really share the data behind these.

There is a side issue with the second example above, in that if the SKU is NULL it doesn't include the row, the top one does include it, so is still the preferred query at present.

Update #1

Based off of the answer: https://stackoverflow.com/a/54202562/8369600

I originally updated my code the format from part 1 of the answer and that works fine.

I have then used the code in step2 and made some minor changes to the formatting to work within phpMyAdmin for me, these changes were:

SELECT
    (SELECT meta_value
        FROM `myprefix_postmeta` as postmeta 
        WHERE 
            postmeta.meta_key = '_sku' 
            AND postmeta.post_id = ( -- Substitute meta_value with CASE-expressions 
                    CASE
                        WHEN IM.meta_key = '_product_id' THEN IM.meta_value
                        ELSE NULL
                    END 
                ) -- IM3.meta_value               
            ) AS sku,
            -- think above is okay ^^^^
    I.order_item_name AS title,
    SUM(
        CASE -- Substitute meta_value with CASE-expressions 
            WHEN IM.meta_key = '_qty' THEN IM.meta_value
            ELSE 0 
        END 
    ) AS quantity,
    -- IM2.meta_value ) AS total
SUM( -- Substitute meta_value with CASE-expressions 
    CASE
            WHEN IM.meta_key = '_line_total' THEN IM.meta_value
            ELSE 0
    END 
) AS total       

FROM 
    `myprefix_woocommerce_order_items` AS I
    LEFT JOIN `myprefix_posts` AS posts 
    ON posts.ID = I.order_id
    LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS IM -- Make readable aliases
        ON IM.order_item_id = I.order_item_id 
            AND IM.meta_key IN('_qty', '_line_total', '_product_id')
    --         AND IM.meta_key = '_qty'   -- Move predicates from WHERE
    -- LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS IM2 
    --     ON IM2.order_item_id =   I.order_item_id
    --         AND IM2.meta_key = '_line_total' -- Move predicates from WHERE
    -- LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS IM3 
    --     ON IM3.order_item_id =   I.order_item_id
    --         AND IM3.meta_key = '_product_id' -- Move predicates from WHERE
WHERE 
    posts.post_type = 'shop_order'
    AND posts.post_status IN
    ( 'wc-completed', 'wc-processing', 'wc-on-hold' )
GROUP BY 
    (
        CASE
            WHEN IM.meta_key = '_product_id' THEN IM.meta_value
            ELSE NULL
        END 
    ) -- IM3.meta_value    
ORDER BY 
    total DESC

However this returns:

Before all the rows were populated with data, suspect the cases aren't working as per the original.

Note I have also added a link to the WooCommerce database documentation as requested in one of the previous comments.

Many (all?) of your left joins return regular inner join result. To get true left result, move the right side table conditions from WHERE to ON. (Or switch to inner join.) — jarlh, Jan 15 '19 at 15:37
Why are you using `LEFT JOIN`? And are you aware that your `WHERE` clause effectively turn them back in to `INNER JOIN`s? Do you have the option to add indexes to the table? *(Include in your question the table definitions, in particular the indexes they have)*. Have you run `EXPLAIN` to see what is taking the most time? *(If so, please show the results in your question.)* — MatBailie, Jan 15 '19 at 15:38
Using inner joins instead of left joins might make your query easier for the query optimizer to analyze, and for this particular query it will not change the results. — John Bollinger, Jan 15 '19 at 15:39
Once again and only in MySQL: the improper use of `GROUP BY`. This query will fail in all other SQL-compliant databases as non-aggregated columns (`postmeta.meta_value` and `woocommerce_order_items.order_item_name`) are not included in `GROUP BY` clause. You may be getting inconsistent results. The [default setting](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-setting) in MySQL 8 runs `ONLY FULL GROUP BY` mode on. — Parfait, Jan 15 '19 at 15:50
No reason for using `LEFT JOIN` specifically - if a more optimal method please send an answer. In regards to adding indexes could you explain? the purpose of this is purely to give me an array of results in PHP, the query will be run in PHP. — Steveo, Jan 15 '19 at 15:52
You JOINed the same table with the same predicate 3 times. You don't need it. Remove 2 unnneeded JOINs to `myprefix_woocommerce_order_itemmeta` and call it a day — Alex Yu, Jan 15 '19 at 15:56
@Ingaz these currently allow me to get the 3 different column values for _qty, _line_total, and _product_id. On initial thoughts I had the same feeling as you - but I can't this to return results when removing them, if you want to show me an example I can attempt to run this. — Steveo, Jan 15 '19 at 16:05
@Steveo - you don't need 3 JOINs to have any amount of projections from the same data — Alex Yu, Jan 15 '19 at 16:08
Ha! You don't need 3 `LEFT OUTER JOIN`s but you need 3 OR-ed predicates for `meta_key` in WHERE. I will edit my answer - look 2-3 minutes later — Alex Yu, Jan 15 '19 at 16:49
MySQL is pretty good at turning `LEFT JOIN` into `JOIN` when appropriate. Also, `ON` clauses that could be put into `WHERE` are done so by the Optimizer. (For readability, you should use `ON` to show how the tables are related.) — Rick James, Jan 15 '19 at 18:14

Alex Yu · Answer 1 · 2019-01-15T17:32:44.760

Step 1: Make readable aliases and move `meta_key`-predicates into `JOIN`s

I took liberty to format you query for clarity.

I renamed aliases and move meta_key-predicates from WHERE to JOINs:

SELECT
    (SELECT meta_value
        FROM `myprefix_postmeta` as postmeta 
        WHERE 
            postmeta.meta_key = '_sku' 
            AND postmeta.post_id = IM3.meta_value ) AS sku,
    I.order_item_name AS title,
    SUM (IM.meta_value ) AS quantity,
    SUM (IM2.meta_value ) AS total
FROM 
    `myprefix_woocommerce_order_items` AS I
    LEFT JOIN `myprefix_posts` AS posts 
    ON posts.ID = I.order_id
    LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS IM -- Make readable aliases
        ON IM.order_item_id = I.order_item_id 
            AND IM.meta_key = '_qty'   -- Move predicates from WHERE
    LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS IM2 
        ON IM2.order_item_id =   I.order_item_id
            AND IM2.meta_key = '_line_total' -- Move predicates from WHERE
    LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS IM3 
        ON IM3.order_item_id =   I.order_item_id
            AND IM3.meta_key = '_product_id' -- Move predicates from WHERE
WHERE 
    posts.post_type = 'shop_order'
    AND posts.post_status IN
    ( 'wc-completed', 'wc-processing', 'wc-on-hold' )
GROUP BY 
    IM3.meta_value
ORDER BY 
    total DESC

Now we have absolutely equivalent query.

Let's look what we can do further.

3 LEFT JOINs to myprefix_woocommerce_order_itemmeta with the same predicate IM.order_item_id = I.order_item_id looks unnecesary duplication
But we have different predicates on meta_key

Step 2: Substitute 3 JOINs with only one, substitute JOINed-fields with CASE-expressions

So we can:

a. Substitute 3 JOINs with only one JOIN

b. Substitute IM/IM2/IM3 fields with CASE-expressions

SELECT
    (SELECT meta_value
        FROM `myprefix_postmeta` as postmeta 
        WHERE 
            postmeta.meta_key = '_sku' 
            AND postmeta.post_id = ( -- Substitute meta_value with CASE-expressions 
                    CASE
                        WHEN IM.meta_key = '_product_id' THEN IM.meta_value
                        ELSE NULL
                    END 
                ) -- IM3.meta_value               
            ) AS sku,
    I.order_item_name AS title,
    SUM (
        CASE -- Substitute meta_value with CASE-expressions 
            WHEN IM.meta_key = '_qty' THEN IM.meta_value
            ELSE 0 
        END 
    ) AS quantity
    -- IM2.meta_value ) AS total
SUM ( -- Substitute meta_value with CASE-expressions 
    CASE
            WHEN IM.meta_key = '_line_total' THEN IM.meta_value
            ELSE 0
    END 
) AS total       

FROM 
    `myprefix_woocommerce_order_items` AS I
    LEFT JOIN `myprefix_posts` AS posts 
    ON posts.ID = I.order_id
    LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS IM -- Make readable aliases
        ON IM.order_item_id = I.order_item_id 
            AND IM.meta_key IN ('_qty', '_line_total', '_product_id')
    --         AND IM.meta_key = '_qty'   -- Move predicates from WHERE
    -- LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS IM2 
    --     ON IM2.order_item_id =   I.order_item_id
    --         AND IM2.meta_key = '_line_total' -- Move predicates from WHERE
    -- LEFT JOIN `myprefix_woocommerce_order_itemmeta` AS IM3 
    --     ON IM3.order_item_id =   I.order_item_id
    --         AND IM3.meta_key = '_product_id' -- Move predicates from WHERE
WHERE 
    posts.post_type = 'shop_order'
    AND posts.post_status IN
    ( 'wc-completed', 'wc-processing', 'wc-on-hold' )
GROUP BY 
    (
        CASE
            WHEN IM.meta_key = '_product_id' THEN IM.meta_value
            ELSE NULL
        END 
    ) -- IM3.meta_value    
ORDER BY 
    total DESC

PS

It's not very comfortable to write SQL without data.

And it's possible that this query is not correct for every version of mysql.

So try understand the principles not just copy-paste my code.

Inform us about results or problems.

OP appears to be running an [entity-attribute value model](https://stackoverflow.com/questions/870808/entity-attribute-value-database-vs-strict-relational-model-ecommerce) and hence needs to self join the table, *myprefix_woocommerce_order_itemmeta*, three times. — Parfait, Jan 15 '19 at 16:12
Thanks for taking the time to answer, with this I get "Unknown column 'woocommerce_order_items.order_item_name' in 'field list'" Looks like that is now defined as `I`, but replacing with `I.order_item_name` then triggers the error returns SUM does not exist. — Steveo, Jan 15 '19 at 16:16
Of course! You need to rename aliased columns accordingly. I will edit answer — Alex Yu, Jan 15 '19 at 16:43
@Ingaz I have updated my answer with details on how attempting this went. — Steveo, Jan 16 '19 at 10:38

Rick James · Answer 2 · 2019-01-16T19:59:56.373

If woocommerce is an offshoot of WordPress, then they probably did a poor job of indexing myprefix_woocommerce_order_itemmeta. Adapt the tips in here for an improvement in the performance.

I agree with others -- The EAV schema pattern sucks. My index suggestions above help some.

For example, and "order" always has a qty, product_id, line_total; so why not have those as real columns instead if hiding them in another table as if they were rare attributes? EAV is somewhat necessary for a shopping site where only some items have 'dress size' or 'F-stop' or 'transmission type'. (In this case, JSON may be a better pattern than EAV.)

Schema review

Yuck, woocommerce is worse than WP (reference):

CREATE TABLE {$wpdb->prefix}woocommerce_order_itemmeta (
  meta_id BIGINT UNSIGNED NOT NULL auto_increment,
  order_item_id BIGINT UNSIGNED NOT NULL,
  meta_key varchar(255) default NULL,
  meta_value longtext NULL,
  PRIMARY KEY  (meta_id),
  KEY order_item_id (order_item_id),
  KEY meta_key (meta_key(32))
) $collate;

Prefix indexing (meta_key(32)) is rarely useful, and often counter-productive. Still, probably all my recommendations (see link above) apply, with suitable name changes.

score 0 · Answer 3 · answered Jan 15 '19 at 15:41

First thing to do is to check if you have proper indexes on all columns where you LEFT JOIN and where you filter. Usually the lack of indexes is the cause for slow queries.

If you want to go further I would do a rebuild of indexes. Maybe some fragmentation is also slowing things down.

Although not essential you could also try to make migrate keys like wc-completed, wc-processing into their own table and make joins by INT.

You could also try to normalize SUMs or even create a computed column for them.

In MySQL, rebuilding indexes is almost _never_ worth the effort. The slowdown due to fragmentation is minuscule. — Rick James, Jan 15 '19 at 18:10

How can I make this MySQL query (with a sub query) faster?

3 Answers3

Step 1: Make readable aliases and move meta_key-predicates into JOINs

Step 2: Substitute 3 JOINs with only one, substitute JOINed-fields with CASE-expressions

PS

Step 1: Make readable aliases and move `meta_key`-predicates into `JOIN`s