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.
3
Answers
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.
Step 1: Make readable aliases and move
meta_key
-predicates intoJOIN
sI took liberty to format you query for clarity.
I renamed aliases and move
meta_key
-predicates fromWHERE
toJOIN
s:Now we have absolutely equivalent query.
Let’s look what we can do further.
LEFT JOIN
s tomyprefix_woocommerce_order_itemmeta
with the same predicateIM.order_item_id = I.order_item_id
looks unnecesary duplicationmeta_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
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.
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):
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.