[wp-trac] [WordPress Trac] #41054: Use sargable date filtering where possible

[WordPress Trac]

#41054: Use sargable date filtering where possible
--------------------------+-----------------------------
 Reporter:  ComputerGuru  |      Owner:
     Type:  enhancement   |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Query         |    Version:  trunk
 Severity:  normal        |   Keywords:
  Focuses:  performance   |
--------------------------+-----------------------------
 Currently, many queries generated by WP use post_date in a non-sargable
 fashion, namely by filtering based on the output of a MySQL function
 taking post_date as a parameter.

 These can be easily rewritten to use the index on post_date without, to my
 eyes, breaking anything to boost performance.

 Here's an example:

 {{{
 MariaDB [blog]> EXPLAIN SELECT * FROM blog.wp_posts WHERE YEAR(post_date)
 = 2017;
 +------+-------------+----------+------+---------------+------+---------+------+------+-------------+
 | id   | select_type | table    | type | possible_keys | key  | key_len |
 ref  | rows | Extra       |
 +------+-------------+----------+------+---------------+------+---------+------+------+-------------+
 |    1 | SIMPLE      | wp_posts | ALL  | NULL          | NULL | NULL    |
 NULL | 2684 | Using where |
 +------+-------------+----------+------+---------------+------+---------+------+------+-------------+

 }}}

 vs

 {{{
 MariaDB [blog]> EXPLAIN SELECT * FROM blog.wp_posts WHERE post_date >=
 "2017-01-01" AND post_date < "2018-01-01";
 +------+-------------+----------+-------+---------------+-----------+---------+------+------+-----------------------+
 | id   | select_type | table    | type  | possible_keys | key       |
 key_len | ref  | rows | Extra                 |
 +------+-------------+----------+-------+---------------+-----------+---------+------+------+-----------------------+
 |    1 | SIMPLE      | wp_posts | range | post_date     | post_date | 8
 | NULL |  262 | Using index condition |
 +------+-------------+----------+-------+---------------+-----------+---------+------+------+-----------------------+

 }}}

 This optimization can be applied to any comparison between post_date and
 an already known parameter (from query_var). The only time it wouldn't be
 possible would be when comparing a portion of the date of two different
 posts (e.g. WHERE MONTH(x.post_date) == MONTH(y.post_date))


 It's not much, but it's something.

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