[wp-trac] [WordPress Trac] #49278: Improve meta query

[WordPress Trac]

#49278: Improve meta query
-------------------------------------------------+-------------------------
 Reporter:  jillebehm                            |       Owner:  (none)
     Type:  enhancement                          |      Status:  reopened
 Priority:  normal                               |   Milestone:
Component:  Query                                |     Version:  5.3.2
 Severity:  normal                               |  Resolution:
 Keywords:  has-patch dev-feedback early early-  |     Focuses:
  like-actually-early has-unit-tests needs-      |  performance
  refresh                                        |
-------------------------------------------------+-------------------------

Comment (by rjasdfiii):

 Looking at the report again [belatedly!], I have to agree that moving the
 AND to the ON clause is important and "required":

 Bad:


 {{{
     INNER JOIN  riff19_postmeta AS mt3  ON ( riff19_posts.ID = mt3.post_id
 )
     WHERE ( blah-blah )
             OR ( this-and-that
                    AND  ( mt3.meta_key = 'pinplugin_event_end_time' )
                )

 }}}

 Good:


 {{{
     INNER JOIN  riff19_postmeta AS mt3  ON ( riff19_posts.ID = mt3.post_id
                   AND  mt3.meta_key = 'pinplugin_event_end_time'

 }}}
                                            )

 Explanation:  The only "meta_key" of interest for "mt3" is
 'pinplugin_event_end_time'.  This is what the "Good" version says.  The
 "Bad" version tests the meta_key only some of the time.

 (Similarly for the other JOINs to postmeta.)

 Putting the test in the ON emphasizes my point.  This produces identical
 results, but is less clear:


 {{{
     INNER JOIN  riff19_postmeta AS mt3  ON ( riff19_posts.ID = mt3.post_id
 )
     WHERE mt3.meta_key = 'pinplugin_event_end_time'
       AND ( blah-blah )
             OR ( this-and-that
                    AND  ( mt3.meta_key = 'pinplugin_event_end_time' )  --
 now redundant
                )

 }}}

 With the Good version, plus the improved indexes, the lookup in mt3 can
 use the combined index on post_id and meta_key, hence the dramatic
 speedup.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/49278#comment:33>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform