Opened 15 years ago
Closed 14 years ago
#14035 closed defect (bug) (fixed)
get_boundary_post() sorts by ID rather than creation date
Reported by: |
|
Owned by: | |
---|---|---|---|
Milestone: | 3.1 | Priority: | normal |
Severity: | normal | Version: | 3.0.1 |
Component: | Performance | Keywords: | has-patch commit |
Focuses: | Cc: |
Description
When get_boundary_post calls get_posts, posts are sorted by their ID rather than creation date. In other words, if old posts were imported or post IDs are skewed (for whatever reason), the 'start' <link /> generated in the header will not be accurate.
I propose sorting by post creation date. Patch attached.
Attachments (3)
Change History (14)
#5
@
14 years ago
This bug is a more serious problem than it initially appears. It can cause performance problems on WordPress sites with thousands of posts.
The reason is that the final SQL it creates when sorting by ID is:
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY wp_posts.ID ASC LIMIT 0, 1;
However, there is no valid MySQL index on this combination. So MySQL loads all the data from the entire wp_posts table where wp_posts.post_type = 'post' and wp_posts.post_status = 'publish' (which is most of them), then does a filesort, then throws away all but one of the rows.
Here's the MySQL "EXPLAIN" output for one of these queries:
mysql> explain SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY wp_posts.ID ASC LIMIT 0, 1; +----+-------------+----------+------+------------------+------------------+---------+-------------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+------------------+------------------+---------+-------------+------+-----------------------------+ | 1 | SIMPLE | wp_posts | ref | type_status_date | type_status_date | 124 | const,const | 3792 | Using where; Using filesort | +----+-------------+----------+------+------------------+------------------+---------+-------------+------+-----------------------------+ 1 row in set (0.00 sec)
Compare this to the "EXPLAIN" output if the bug is fixed and it correctly sorts by post_date:
mysql> explain SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY wp_posts.post_date ASC LIMIT 0, 1; +----+-------------+----------+------+------------------+------------------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+------------------+------------------+---------+-------------+------+-------------+ | 1 | SIMPLE | wp_posts | ref | type_status_date | type_status_date | 124 | const,const | 3793 | Using where | +----+-------------+----------+------+------------------+------------------+---------+-------------+------+-------------+ 1 row in set (0.00 sec)
There's no "filesort" in the correct version (and "filesort" is bad).
When the wp_posts table isn't in the MySQL or disk cache, I've seen the first version take more than 5 seconds occasionally (it shows up in the MySQL slow query log).
So fixing this bug will not only correct the behavior of the link in the post header; it will avoid a significant performance issue.
(The attachment 14035.diff patch appears to be correct, by the way.)
#7
@
14 years ago
- Component changed from General to Performance
- Keywords commit added
- Milestone changed from Awaiting Triage to 3.1
PS: The 'no_found_rows' => true
bit can be ommited due to [15548].
#8
@
14 years ago
Besides performance, this is actually a logical error too, given the function's description. See 14035.2.diff
Actually, I think the orderby parameter should be left out, so that the default is used.