Optimize SQL Query (Slow Performance) WordPress

• SOLVED

I have a custom SQL query I am writing to gather a list of post_ids where the following conditions are met:

1) There is an entry for it in wp_postmeta with a meta_key of 'product_ids' and a meta_value of a specific value (ex: 100).

2) For those returned in #1, there is either:
a) an entry for the post_id in wp_postmeta with a meta_key of 'usage_count' and a meta_value of 0.
OR
a) no entry at all for the post_id in wp_postmeta with a meta_key of 'usage_count'


I have written the following query to accomplish this:


SELECT w.post_id FROM wp_postmeta w
LEFT JOIN (
SELECT post_id, meta_value FROM wp_postmeta
WHERE meta_key='usage_count') p
ON w.post_id = p.post_id
WHERE
( w.meta_key = 'product_ids'
AND w.meta_value = '100' )
AND
( p.meta_value = '0'
OR p.meta_value IS NULL )


This query works and returns the expected values. However... it is <strong>SLOW</strong>.

Working on my test environment the cause seems to be the <strong>'p.meta_value IS NULL'</strong> condition. With this the query execution time goes from 0.0880s -> 10.2345s.

I am hoping someone has a more efficient way to write this to reduce the execution time. There is a SQL Fiddle below with the layout / query as example.

[[LINK href="http://sqlfiddle.com/#!2/9b292/9/0"]]http://sqlfiddle.com/#!2/9b292/9/0[[/LINK]]

Answers (1)

2014-08-01

John Cotton answers:

Hard to tell how much quicker with such a small sample, but here's how I'd do it. It's a shorted execution plan.


SELECT w.post_id FROM wp_postmeta w
LEFT JOIN wp_postmeta pm2 ON w.post_id = pm2.post_id AND pm2.meta_key = 'usage_count'
WHERE
w.meta_key = 'product_ids' AND w.meta_value = '100' AND (pm2.meta_value IS NULL OR pm2.meta_value = 0)



---

Chuck Mac comments:

You rock =)

mine:

<em>Showing rows 0 - 211 (212 total, Query took 9.2381 sec)</em>

Yours:

<em>Showing rows 0 - 211 (212 total, Query took 0.1469 sec)</em>