[wp-trac] [WordPress Trac] #42883: Use sargable queries for date-based lookups for posts

[WordPress Trac]

#42883: Use sargable queries for date-based lookups for posts
--------------------------+-----------------------------
 Reporter:  ComputerGuru  |      Owner:
     Type:  enhancement   |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  trunk
 Severity:  normal        |   Keywords:
  Focuses:  performance   |
--------------------------+-----------------------------
 Related to #41054 but a very specific and actionable, high-impact instance
 is the fact that the WordPress lookup for permalinks involving dates is
 not sargable.

 For a bog-standard permalink structure %year%/%slug%/, WP generates the
 following query:


 {{{
 SELECT wp_posts.*
 FROM wp_posts
 WHERE 1=1
 AND ( YEAR( wp_posts.post_date ) = 2017 )
 AND wp_posts.post_name = 'tahoma-vs-verdana'
 AND wp_posts.post_type = 'post'
 ORDER BY wp_posts.post_date DESC
 }}}

 This runs (as a cold query) in ~0.075 seconds on a dedicated (and
 overpowered) MariaDB 10 instance on a pretty small WordPress DB. While
 indexes exist for all the fields matched against in the query, the use of
 {{{AND ( YEAR( wp_posts.post_date ) = 2017 )}}} cannot be matched against
 the database because MySQL/MariaDB is not intelligent enough to optimize
 that constraint.

 The "same" query adjusted to make the match against {{{post_date}}}
 sargable does the same in ~0.034 seconds (half the time):

 {{{
 SELECT wp_posts.*
 FROM wp_posts
 WHERE 1=1
 AND wp_posts.post_date >= DATE("2017-01-01")
 AND wp_posts.post_date < DATE("2018-01-01")
 AND wp_posts.post_name = 'tahoma-vs-verdana'
 AND wp_posts.post_type = 'post'
 ORDER BY wp_posts.post_date DESC
 }}}

 The same would apply for permalinks that reference the month and day, of
 course.

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