need help optimizing wordpress meta_query

3 comments

1. So far I have:

   SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
   WHERE ID IN (
               SELECT post_id from wp_postmeta 
               WHERE 
               (
                   (meta_key = 'csv_product_consumption' AND CAST(meta_value AS SIGNED) BETWEEN 0 AND 2) 
                   OR (meta_key = 'csv_product_consumption' AND CAST(meta_value AS SIGNED) BETWEEN 3 AND 9) 
                   OR (meta_key = 'csv_product_consumption' AND CAST(meta_value AS SIGNED) BETWEEN 10 AND 19) 
                   OR (meta_key = 'csv_product_download' AND CAST(meta_value AS SIGNED) BETWEEN 2 AND 9) 
                   OR (meta_key = 'csv_product_download' AND CAST(meta_value AS SIGNED) BETWEEN 10 AND 19) 
                   OR (meta_key = 'csv_product_download' AND CAST(meta_value AS SIGNED) BETWEEN 20 AND 29) 
                   OR (meta_key = 'csv_product_month_sub' AND CAST(meta_value AS SIGNED) BETWEEN 0 AND 49) 
                   OR (meta_key = 'csv_product_month_sub' AND CAST(meta_value AS SIGNED) BETWEEN 50 AND 99) 
                   OR (meta_key = 'csv_product_month_sub' AND CAST(meta_value AS SIGNED) BETWEEN 100 AND 149) 
               )
               GROUP BY post_id
           )
   AND wp_posts.post_type = 'produkter' 
   AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
   AND ( (wp_postmeta.meta_key = 'csv_product_type') AND (wp_postmeta.meta_value = 'Mobilt Bredbaand'))
   ORDER BY wp_postmeta.meta_value+0 ASC
   

   It no longer uses too much cpu and the query takes 0.0331 sec.

   Any other ideas would be appreciated.

   Log in to Reply

2. I came across this problem and realized the issue was with all the INNER JOINS generated by WordPress. I got the raw query from WordPress:

   SELECT   wp_posts.* FROM wp_posts  
   INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
   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) WHERE 1=1  AND ( wp_term_relationships.term_taxonomy_id IN (7,11,12,13,14,15) AND wp_posts.ID NOT IN (
                       SELECT object_id
                       FROM wp_term_relationships
                       WHERE term_taxonomy_id IN (10)
                   ) ) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'product', 'store_page') AND (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key = 'product_type' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground')
   OR  (mt1.meta_key = 'product_type2' AND CAST(mt1.meta_value AS CHAR) = 'type_pre_ground')
   OR  (mt2.meta_key = 'product_type3' AND CAST(mt2.meta_value AS CHAR) = 'type_pre_ground')
   OR  (mt3.meta_key = 'product_type4' AND CAST(mt3.meta_value AS CHAR) = 'type_pre_ground')
   OR  (mt4.meta_key = 'product_type5' AND CAST(mt4.meta_value AS CHAR) = 'type_pre_ground')
   OR  (mt5.meta_key = 'product_type6' AND CAST(mt5.meta_value AS CHAR) = 'type_pre_ground') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC
   

   This never completed and tied up the CPU at a very high load. I removed the last two INNER JOINs (and corresponding WHERE clauses) and got results in 2 seconds:

   SELECT   wp_posts.* FROM wp_posts  
   INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
   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) WHERE 1=1  AND ( wp_term_relationships.term_taxonomy_id IN (7,11,12,13,14,15) AND wp_posts.ID NOT IN (
                       SELECT object_id
                       FROM wp_term_relationships
                       WHERE term_taxonomy_id IN (10)
                   ) ) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'product', 'store_page') AND (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key = 'product_type' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground')
   OR  (mt1.meta_key = 'product_type2' AND CAST(mt1.meta_value AS CHAR) = 'type_pre_ground')
   OR  (mt2.meta_key = 'product_type3' AND CAST(mt2.meta_value AS CHAR) = 'type_pre_ground')
   OR  (mt3.meta_key = 'product_type4' AND CAST(mt3.meta_value AS CHAR) = 'type_pre_ground') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC 
   

   So I realized that removing the INNER JOINS was the key to speeding up the query. I re-wrote the query with only one wp_postmeta INNER JOIN and got results in fractions of a second:

   SELECT   wp_posts.* FROM wp_posts  
   INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
   INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1  AND ( wp_term_relationships.term_taxonomy_id IN (7,11,12,13,14,15) AND wp_posts.ID NOT IN (
                       SELECT object_id
                       FROM wp_term_relationships
                       WHERE term_taxonomy_id IN (10)
                   ) ) 
   AND wp_posts.post_type IN ('post', 'page', 'attachment', 'product', 'store_page') 
   AND (wp_posts.post_status = 'publish') 
   AND ( (wp_postmeta.meta_key = 'product_type' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground')
   OR  (wp_postmeta.meta_key = 'product_type2' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground')
   OR  (wp_postmeta.meta_key = 'product_type3' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground')
   OR  (wp_postmeta.meta_key = 'product_type4' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground')
   OR  (wp_postmeta.meta_key = 'product_type5' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground')
   OR  (wp_postmeta.meta_key = 'product_type6' AND CAST(wp_postmeta.meta_value AS CHAR) = 'type_pre_ground') ) 
   GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC
   

   With this solution I created a WordPress filter to convert the postmeta query to the faster format:

   function custom_get_meta_sql( $meta_sql )
   {
       global $wpdb;
   
       $posts_table = $wpdb->prefix . 'posts';
       $postmeta_table = $wpdb->prefix . 'postmeta';
   
       //use single INNER JOIN
       $meta_sql['join'] = " INNER JOIN {$postmeta_table} AS pmta ON ({$posts_table}.ID = pmta.post_id) ";
   
       //replace the mtNN aliases with wp_postmeta
       $where_clause = $meta_sql['where'];
       $where_clause = str_replace("{$postmeta_table}.", 'pmta.', $where_clause);
       $where_clause = preg_replace('/mtd+.meta_/i', 'pmta.meta_', $where_clause);
   
       $meta_sql['where'] = $where_clause;
       return $meta_sql;
   }
   
   add_filter( 'get_meta_sql', 'custom_get_meta_sql' );
   

   This filter should be located in the functions.php file of your WordPress Theme.

   Log in to Reply

3. This is an old post now, but you might consider trying Horizontal Meta. Horizontal Meta monitors specific keys in the post & user meta tables and extracts them out into a relational/horizontal format. You can still use the WordPress query engine to run queries, but Horizontal Meta will rewrite the queries to speed it up. Available here: http://wordpress.org/plugins/horizontal-meta/

   Log in to Reply