I am trying to puzzle out an easy method to create the next query that's produced by WordPress's WP_Query class. Right it is now very slow.

SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id)
INNER JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id)
INNER JOIN wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id)
INNER JOIN wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id)
INNER JOIN wp_postmeta AS mt7 ON (wp_posts.ID = mt7.post_id)
INNER JOIN wp_postmeta AS mt8 ON (wp_posts.ID = mt8.post_id)

WHERE 1=1 AND wp_posts.post_type = 'gemstone'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private')
AND (wp_postmeta.meta_key = 'gemstone_active_price'
    AND (mt1.meta_key = 'gemstone_status' AND CAST(mt1.meta_value AS CHAR) = 'Available')
    AND (mt2.meta_key = 'gemstone_length' AND CAST(mt2.meta_value AS DECIMAL(10,2)) BETWEEN '0' AND '9')
    AND (mt3.meta_key = 'gemstone_width' AND CAST(mt3.meta_value AS DECIMAL(10,2)) BETWEEN '0' AND '9')
    AND (mt4.meta_key = 'gemstone_depth' AND CAST(mt4.meta_value AS DECIMAL(10,2)) BETWEEN '0' AND '7')
    AND (mt5.meta_key = 'gemstone_color' AND CAST(mt5.meta_value AS CHAR) IN ('L','K','J','I','H','G','F','E','D'))
    AND (mt6.meta_key = 'gemstone_clarity' AND CAST(mt6.meta_value AS CHAR) IN ('I3','I2','I1','SI2','SI1','VS2','VVS2','VVS1','IF','FL'))
    AND (mt7.meta_key = 'gemstone_weight' AND CAST(mt7.meta_value AS DECIMAL(10,2)) BETWEEN '0.67' AND '1.85')
    AND (mt8.meta_key = 'gemstone_active_price' AND CAST(mt8.meta_value AS DECIMAL(10,2)) BETWEEN '960' AND '2300')
)

GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 ASC
LIMIT 0, 20

Yes, it appears like a large mess, however the whole factor executes very rapidly after i not have the 2 IN() claims within the WHERE clause (mt5 and mt6 above). The issue is, I'm not sure SQL good enough to determine a different way to write the query that eliminates while using IN() claims. Any ideas?

UPDATE: This is actually the EXPLAIN output with this query just in case it can help anybody. If anybody has every other ideas, I am available to anything. It has me completely stumped.

id  select_type     table           type    possible_keys               key         key_len     ref                         rows    Extra
1   SIMPLE          wp_postmeta     ref     post_id,meta_key            meta_key    768         const                       2       Using where; Using temporary; Using filesort
1   SIMPLE          mt1             ref     post_id,meta_key            post_id     8           db.wp_postmeta.post_id      2       Using where
1   SIMPLE          mt2             ref     post_id,meta_key            post_id     8           db.mt1.post_id              2       Using where
1   SIMPLE          mt3             ref     post_id,meta_key            post_id     8           db.wp_postmeta.post_id      2       Using where
1   SIMPLE          mt4             ref     post_id,meta_key            post_id     8           db.mt2.post_id              2       Using where
1   SIMPLE          mt5             ref     post_id,meta_key            post_id     8           db.wp_postmeta.post_id      2       Using where
1   SIMPLE          mt6             ref     post_id,meta_key            post_id     8           db.wp_postmeta.post_id      2       Using where
1   SIMPLE          mt7             ref     post_id,meta_key            post_id     8           db.mt3.post_id              2       Using where
1   SIMPLE          mt8             ref     post_id,meta_key            post_id     8           db.wp_postmeta.post_id      2       Using where
1   SIMPLE          wp_posts        eq_ref  PRIMARY,type_status_date    PRIMARY     8           db.wp_postmeta.post_id      1       Using where

UPDATE 2: After more experimentation, I've recognized that it's not only the IN() claims which are slowing down this question lower. It appears that any combination in excess of one IN() using more than 3 BETWEEN...AND... claims includes a dramatic effect on performance.

For instance, the query executes within .04s basically drop the final 2 AND clauses (versus 4.9s together), or it executes ins04s basically drop the two AND clauses using the IN() claims. This leads me to consider that the 2 query solution would be better, however i have no clue how you can implement that through the WordPress WP_Query API, and when Used to do, I question in the event that would be also faster than simply doing one query after which blocking the outcomes via PHP.

I personally don't like the thought of blocking with PHP because I have read in a number of locations that blocking ought to be left towards the database because that is what databases are great at. Incidentally, whether it makes any difference, I am running these queries on the WordPress 3.3.1 installation on my small localhost WAMP server on the computer with lots of processing energy (Apple i7, 12 GB RAM, etc.).

UPDATE 3: I believed about just quitting and getting rid of all of the IN() clauses in the query and filter for individuals via PHP, but with a serious draw backs. Besides being inefficient along with a code smell, it will not let me control my pagination properly. When things are strained within the database, I'm able to simply employ the LIMIT clause to deal with pagination. After I filter with PHP, I've no clue the number of results is going to be came back for just about any given offset. So, all of the blocking really must be completed in the database, now you ask , how. Does anybody have additional suggestions whatsoever for me personally? Would any extra information be useful to anybody?