[wp-trac] [WordPress Trac] #31171: Very slow db query in get_lastpostmodified() which is called by WP feeds results in large table scan and sort using filesort

[WordPress Trac]

#31171: Very slow db query in get_lastpostmodified() which is called by WP feeds
results in large table scan and sort using filesort
--------------------------+-----------------------------
 Reporter:  archon810     |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Feeds         |    Version:  4.1
 Severity:  normal        |   Keywords:
  Focuses:  performance   |
--------------------------+-----------------------------
 Hey guys,

 In my quest to improve performance of core Wordpress functionality (see
 #31071 and #31072), I'm back with another optimization that is quite
 significant for large WP installations.

 As far as I can tell, the issue comes up in a function called
 `_get_last_post_time()`, which is used by `get_lastpostmodified()` and in
 turn all the feeds `feed-rss2.php`, etc.

 The query is as follows:
 {{{
 SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND
 post_type IN ('post', 'page', 'attachment') ORDER BY post_modified_gmt
 DESC LIMIT 1
 }}}

 In our database with 286k rows in wp_posts, this query scans 22,471 rows
 and then sorts using filesort - an especially nasty operation.

 Here is the result of EXPLAIN:
 {{{
 +----+-------------+----------+-------+-----------------------------------------------------------------+------------------+---------+------+-------+-----------------------------+
 | id | select_type | table    | type  | possible_keys
 | key              | key_len | ref  | rows  | Extra
 |
 +----+-------------+----------+-------+-----------------------------------------------------------------+------------------+---------+------+-------+-----------------------------+
 |  1 | SIMPLE      | wp_posts | range |
 type_status_date,post_status,post_type_mime_type,post_type_date |
 type_status_date | 124     | NULL | 22471 | Using where; Using filesort |
 +----+-------------+----------+-------+-----------------------------------------------------------------+------------------+---------+------+-------+-----------------------------+
 }}}

 No index is used, as you can see.

 Testing it with SQL_NO_CACHE, this query runs anywhere between 1s and 3+s.
 Needless to say, feeds get hit a lot by various crawlers and this query
 gets run a lot too.

 The fix, which results in several orders of magnitude of speed
 improvements is as follows:
 {{{
 CREATE INDEX `modified_gmt_status_type` ON `wp_posts`(`post_modified_gmt`,
 `post_status`, `post_type`);
 }}}

 EXPLAIN says this afterwards:
 {{{
 +----+-------------+----------+-------+-----------------------------------------------------------------+--------------------------+---------+------+------+--------------------------+
 | id | select_type | table    | type  | possible_keys
 | key                      | key_len | ref  | rows | Extra
 |
 +----+-------------+----------+-------+-----------------------------------------------------------------+--------------------------+---------+------+------+--------------------------+
 |  1 | SIMPLE      | wp_posts | index |
 type_status_date,post_status,post_type_mime_type,post_type_date |
 modified_gmt_status_type | 132     | NULL |   13 | Using where; Using
 index |
 +----+-------------+----------+-------+-----------------------------------------------------------------+--------------------------+---------+------+------+--------------------------+
 }}}

 Not only is this query faster to query the database, it even returns the
 result from the index directly, thus running even faster.

 Please consider testing this and adding to WP core.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/31171>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform