Use sargable queries for date-based lookups for posts

[ComputerGuru]

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.

[ComputerGuru]

For reference, I also filed an enhancement request upstream with MariaDB to explore the possibility of automating this transform in the query optimizer: ​https://jira.mariadb.org/browse/MDEV-14635

[boonebgorges]

Hi @ComputerGuru - Thanks very much for the detailed post. This sounds like an excellent enhancement.

Writing this patch will be a medium-sized but self-contained project, probably appropriate for a new contributor. (Specifically, the person writing the enhancement will have to be careful when calculating "end" dates, especially for months.) Our existing test coverage for date queries is probably good enough to cover the changes described here; see https://core.trac.wordpress.org/browser/tags/4.9.1/tests/phpunit/tests/date/query.php and https://core.trac.wordpress.org/browser/tags/4.9.1/tests/phpunit/tests/query/date.php

[Mte90]

first implementation

[Mte90]

With @eatzeni we tried to investigate together starting from a code by him.
At the end we chosen an approach to do a specific method to build a compare part of the query.
We are looking for feedback, but in any case the unit tests are not causing any errors but I cannot estimate the time of running the unit tests because seems that at every run the value change.

[jdgrimes]

I just took a quick look at the patch, and I noticed a couple of things:

• A very small issue, but the parameter descriptions in the method docblock should end in a period.
• It doesn't look to me like the =, !=, etc., comparison operators are being handled properly. Unless I am missing something, they will fall through to the default block in the switch and then just the year value will be returned rather than a full condition.

[spacedmonkey]

I think we should tackle this as part of #36723.

@spacedmonkey was this fix in #36723?