[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:  SergeyBiryukov
     Type:  enhancement             |      Status:  reviewing
 Priority:  normal                  |   Milestone:  Future Release
Component:  Database                |     Version:  3.0.1
 Severity:  normal                  |  Resolution:
 Keywords:  has-patch dev-feedback  |     Focuses:  performance
------------------------------------+-----------------------------

Old description:

> 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.

New description:

 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.

--

Comment (by mukesh27):

 Hi there!

 @simonwheatley PR updated against the Trunk version can you please test if
 it works for you?

 @SergeyBiryukov is this ticket is in your to-do list? Do you think it will
 marge in the upcoming release?

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/15499#comment:34>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform