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!

[OllieJones]

I did some analysis of how widespread queries are that could exploit this change. My very imperfect sample comes from uploaded query-monitoring sessions from Index WP MySQL For Speed. These are uploaded by users when asking for additional help with query performance trouble.

It is true that making date-ranges sargeable is a good idea. What's more, the queries in my sample would, if this change were made, exploit an existing index on (post_type, post_status, post_date).

But my sample of queries shows little use of this particular suboptimal kind of WHERE filter.

And, the documentation for WP_Date_Query suggests that clients use the YEAR() / MONTH() / DAY() sorts of filters. So, fixing this would require a bunch of interesting but ultimately brittle logic to generate date ranges from the query terms presented.

What's more, WP_Query results go into the object cache.

So, I respectfully suggest the dev and test labor, and the potential destabilization, of this tix isn't worth the potential performance improvements.