WordPress.org

Make WordPress Core

Opened 23 months ago

Last modified 20 months ago

#42883 new enhancement

Use sargable queries for date-based lookups for posts

Reported by: ComputerGuru Owned by:
Milestone: Future Release Priority: normal
Severity: normal Version: 3.7
Component: Query Keywords: has-patch dev-feedback
Focuses: performance Cc:
PR Number:

Description

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.

Attachments (1)

42883.diff (2.2 KB) - added by Mte90 21 months ago.
first implementation

Download all attachments as: .zip

Change History (6)

#1 @ComputerGuru
23 months ago

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

#2 @boonebgorges
23 months ago

  • Component changed from Database to Query
  • Keywords needs-patch good-first-bug added
  • Milestone changed from Awaiting Review to Future Release
  • Version changed from trunk to 3.7

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
21 months ago

first implementation

#3 @Mte90
21 months ago

  • Keywords has-patch dev-feedback added; needs-patch good-first-bug removed

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.

Last edited 21 months ago by Mte90 (previous) (diff)

#4 @jdgrimes
20 months ago

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.

This ticket was mentioned in Slack in #core by mte90. View the logs.


20 months ago

Note: See TracTickets for help on using tickets.