Use sargable date filtering where possible

[ComputerGuru]

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.

[johnbillion]

Thanks for the ticket, @ComputerGuru.

Can you provide a patch which demonstrates your proposed change? Some performance benchmark results would be great, too, along with any new unit tests if you think they're necessary.

Thanks in advance!