Opened 6 years ago
Last modified 6 years ago
#41054 new enhancement
Use sargable date filtering where possible
Reported by: |
|
Owned by: | |
---|---|---|---|
Milestone: | Awaiting Review | Priority: | normal |
Severity: | normal | 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 (1)
Note: See
TracTickets for help on using
tickets.
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!