[wp-trac] [WordPress Trac] #38173: Meta query creates unecessary multiple left joins when using the same meta key

[WordPress Trac]

#38173: Meta query creates unecessary multiple left joins when using the same meta
key
--------------------------+-----------------------------
 Reporter:  neonWired     |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  4.6
 Severity:  normal        |   Keywords:
  Focuses:  performance   |
--------------------------+-----------------------------
 If you specify the below as a meta_query wordpress creates an extremely
 bad and inefficient query, it seems to unnecessarily create a left join
 for each array even though they have the same key when it could use the
 same join

 {{{#!php
 <?php
 array(
   'relation' => 'OR',
   array(
      'key' => 'product',
      'value' => '1',
      'compare' => '!='
   ),
   array(
     'key' => 'product',
     'compare' => 'NOT EXISTS'
   )
 );
 }}}


 {{{
 SELECT SQL_CALC_FOUND_ROWS  vvc_posts.ID FROM vvc_posts  LEFT JOIN
 vvc_postmeta ON ( vvc_posts.ID = vvc_postmeta.post_id )  LEFT JOIN
 vvc_postmeta AS mt1 ON (vvc_posts.ID = mt1.post_id AND mt1.meta_key =
 'product' ) WHERE 1=1  AND (
   ( vvc_postmeta.meta_key = 'product' AND CAST(vvc_postmeta.meta_value AS
 CHAR) != '1' )
   OR
   mt1.post_id IS NULL
 ) AND vvc_posts.post_type = 'news' AND ((vvc_posts.post_status =
 'publish')) GROUP BY vvc_posts.ID ORDER BY vvc_posts.post_date DESC LIMIT
 0, 10

 }}}


 On my site this query takes a huge 6.640 sec, more than 80% of the page's
 ttfb.


 {{{
 SELECT SQL_CALC_FOUND_ROWS vvc_posts.ID
 FROM vvc_posts
 LEFT JOIN vvc_postmeta ON ( vvc_posts.ID = vvc_postmeta.post_id &&
 vvc_postmeta.meta_key = 'product')
 WHERE 1=1
 AND (CAST(vvc_postmeta.meta_value AS CHAR) != '1' OR vvc_postmeta.post_id
 IS NULL )
 AND vvc_posts.post_type = 'news'
 GROUP BY vvc_posts.ID
 ORDER BY vvc_posts.post_date
 }}}


 whereas an optimized version takes only 0.969 sec.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/38173>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform