[wp-trac] [WordPress Trac] #15499: Add an index for get_lastpostmodified query

[WordPress Trac]

#15499: Add an index for get_lastpostmodified query
---------------------------+------------------------------------------------
 Reporter:  simonwheatley  |       Owner:                        
     Type:  enhancement    |      Status:  new                   
 Priority:  normal         |   Milestone:  Awaiting Review       
Component:  Database       |     Version:  3.0.1                 
 Severity:  normal         |    Keywords:  has-patch dev-feedback
---------------------------+------------------------------------------------
 I had a friend (Jools Wills) look over a WordPress site recently, to get a
 fresh view on what might be optimised, and he noticed a query which might
 benefit from an additional index on ```WP_Posts```. The query ```SELECT
 post_modified_gmt FROM $wpdb->posts WHERE post_status = 'publish' AND
 post_type = 'post' ORDER BY post_modified_gmt DESC LIMIT 1``` in
 ```get_lastpostmodified``` is run for last modified date in GMT, and
 currently doesn't use an index. This SQL is run whenever certain types of
 feed are requested as far as I can see.

 We added ```CREATE INDEX type_status_modified ON wp_posts (post_type,
 post_status, post_modified_gmt);``` and ```CREATE INDEX
 type_status_modified_no_id ON wp_posts (post_type, post_status,
 post_date_gmt);``` and the query runs a lot faster now. The following
 timings were taken running the first query (```post_modified_gmt```) on a
 36,362 row posts table. Note that it doesn't use filesort after the index
 has been added.

 ''Before:''

 {{{
 mysql> EXPLAIN SELECT post_modified_gmt FROM slgr_posts WHERE post_status
 = 'publish' AND post_type = 'post' ORDER BY post_modified_gmt DESC LIMIT
 1;
 +----+-------------+------------+------+------------------+------------------+---------+-------------+-------+-----------------------------+
 | id | select_type | table      | type | possible_keys    | key
 | key_len | ref         | rows  | Extra                       |
 +----+-------------+------------+------+------------------+------------------+---------+-------------+-------+-----------------------------+
 |  1 | SIMPLE      | slgr_posts | ref  | type_status_date |
 type_status_date | 124     | const,const | 24718 | Using where; Using
 filesort |
 +----+-------------+------------+------+------------------+------------------+---------+-------------+-------+-----------------------------+
 1 row in set (0.03 sec)
 }}}


  * 0.21290683746338ms
  * 0.25690102577209ms
  * 0.230553150177ms
  * 0.2274341583252ms
  * 0.23083996772766ms

 ''After:''

 {{{
 mysql> EXPLAIN SELECT post_modified_gmt FROM slgr_posts WHERE post_status
 = 'publish' AND post_type = 'post' ORDER BY post_modified_gmt DESC LIMIT
 1;
 +----+-------------+------------+------+---------------------------------------+----------------------+---------+-------------+-------+-------------+
 | id | select_type | table      | type | possible_keys
 | key                  | key_len | ref         | rows  | Extra       |
 +----+-------------+------------+------+---------------------------------------+----------------------+---------+-------------+-------+-------------+
 |  1 | SIMPLE      | slgr_posts | ref  |
 type_status_date,type_status_modified | type_status_modified | 124     |
 const,const | 24718 | Using where |
 +----+-------------+------------+------+---------------------------------------+----------------------+---------+-------------+-------+-------------+
 1 row in set (0.00 sec)
 }}}

  * 0.00082707405090332ms
  * 0.00072288513183594ms
  * 0.00074386596679688ms
  * 0.00066494941711426ms
  * 0.00066208839416504ms

 In ```get_lastpostmodified``` both these queries are run, so the total
 savings in my case on a quiet server are nearly 0.5 seconds... worth
 having, I reckon.

 I've not created a patch for schema changes before, but I think the only
 place the change would need to go would be ```scheme.php```? Suggested
 patch attached.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/15499>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software