Reply To: Mistake in documentation ? : Query posts by custom fields

• unikmedia

• October 17, 2015 at 12:40 am

Finally i used wpdb to execute this request:

SELECT
    wp_posts.ID
FROM
    wp_posts
    INNER JOIN    wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
    LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = 'rc_cp_min_0') 
    LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id AND mt2.meta_key = 'rc_cp_max_0') 
    LEFT JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id AND mt3.meta_key = 'rc_cp_min_1') 
    LEFT JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id AND mt4.meta_key = 'rc_cp_max_1') 
    LEFT JOIN wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id AND mt5.meta_key = 'rc_cp_min_2') 
    LEFT JOIN wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id AND mt6.meta_key = 'rc_cp_max_2')
WHERE
    1 = 1
        AND (wp_postmeta.meta_key = 'code pays'
        AND wp_postmeta.meta_value = 'fr')
        AND ((mt1.meta_value <= 7000 AND mt2.meta_value  >= 7000) 
          OR (mt3.meta_value <= 7000 AND mt4.meta_value  >= 7000) 
          OR (mt5.meta_value <= 7000 AND mt6.meta_value  >= 7000))
        AND wp_posts.post_type = 'distributeur'
        AND wp_posts.post_status = 'publish'
LIMIT 0 , 1

Duration: 0.031 sec

It’s exactly the same code except that meta_key conditions are on join and not on where and i replaced INNER JOIN by LEFT JOIN.

My knowledge of Mysql optimization is limited so i don’t know exactly why it’s really faster but it works 🙂