id,summary,reporter,owner,description,type,status,priority,milestone,component,version,severity,resolution,keywords,cc
15499,Add an index for get_lastpostmodified query,simonwheatley,,"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.",enhancement,new,normal,Future Release,Database,3.0.1,normal,,has-patch dev-feedback 3.6-early,