Opened 8 years ago
Last modified 2 months ago
#41054 new enhancement
Use sargable date filtering where possible
Reported by: |
|
Owned by: | |
---|---|---|---|
Milestone: | Awaiting Review | Priority: | normal |
Severity: | trivial | Version: | |
Component: | Database | Keywords: | needs-patch |
Focuses: | performance | Cc: |
Description
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.
Change History (2)
#1
@
8 years ago
- Component changed from Query to Database
- Keywords needs-patch added
- Version trunk deleted
#2
@
2 months ago
- Severity changed from normal to trivial
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.
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!