Increase query performance

Question

I'm trying to optimize this query:

SELECT post_id 
FROM wp_postmeta
WHERE meta_key = 'passenger_group_id'
AND meta_value in (SELECT post_id 
                   FROM wp_postmeta 
                   WHERE meta_key = 'group_event' AND meta_value = '14608')
AND post_id IN (SELECT post_id 
                FROM wp_postmeta 
                WHERE meta_value='Cancelled')
AND post_id NOT IN (SELECT ID 
                    FROM wp_posts 
                    WHERE post_status='trash')

Any help would be greatly appreciated.

What concrete database is this for? Oracle? MySQL? SQL Server? DB2? Something else entirely? Also: what do your tables look like - what columns (and datatypes!) do they have. How many rows are in those tables? What kind of indexes do you have on your tables? Once you're provided all of this - then maybe someone can help..... — marc_s
– marc_s, Commented Oct 15, 2014 at 17:10
@marc_s, those are tables in the Wordpress schema, so this must be MySQL. I've added tags. But your point is right -- people who ask questions should provide DDL for their tables. — Bill Karwin
– Bill Karwin, Commented Oct 15, 2014 at 17:36

radar · Accepted Answer · 2014-10-15 17:23:42Z

1

you can use EXISTS clause alone and add NOT IN condition can be made NOT EXISTS

select wp1.post_id 
from wp_postmeta wp1
WHERE meta_key = 'passenger_group_id'
and exists  ( select 1 FROM
              wp_postmeta wp2
              where ( (wp2.meta_key = 'group_event' AND wp2.meta_value = '14608' ) or wp2.meta_value ='Cancelled' )
              and wp2.post_id = wp1.meta_value 

            )
and not exists ( select 1 from
                 wp_posts p
                 on p.id = wp1.post_id
                 and p.post_status = 'trash')
                )

edited Oct 15, 2014 at 17:23

answered Oct 15, 2014 at 17:15

radar

13.4k2 gold badges27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

xQbert Over a year ago

the not in references a different table than what you've done here. I don't see how this would work given wp_posts isn't in your query.

radar Over a year ago

@FlawlessData, i think needs more changes, as second not in is from different table

Collectives™ on Stack Overflow

Increase query performance

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related