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?
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
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.).
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?