[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:  6.2
Component:  Database                |     Version:  3.0.1
 Severity:  normal                  |  Resolution:
 Keywords:  has-patch dev-feedback  |     Focuses:  performance
------------------------------------+-----------------------------

Comment (by rjasdfiii):

 Some technical info on MySQL.  These will explain some of the timing
 differences mentioned in this thread.  And may help in designing INDEXes
 in the future.

 * "IN ('post', 'page', 'attachment')" is less optimizable than "= 'post'".
 (Note in the slowlog "Rows examined".)
 * It is best to put columns tested with "=" first in the INDEX.  (The
 order of those columns in the index does not matter.)  So, ask which of
 these is usually tested with "=":  post_status or post_type.
 * A "covering index" is one where _all_ the columns that are used
 _anywhere_ in the SELECT are present in the INDEX.  (This explains some of
 the improved times.)  The reason that covering is better is that all the
 work is done in the B+Tree containing the INDEX; no need to reach over to
 the data's B+Tree to fetch more columns.
 * INDEX(post_status, post_type, post_modified_gmt, post_date_gmt) would
 possibly the best overall.  Status is first because of "=".  For all the
 mentioned queries, it would be "covering".
 * WHERE post_status = '...' AND post_type = '...' ... and get
 post_modified_gmt -- This query would be very fast (Rows_examined = 1)
 with the above index.  All other queries would be less efficient -- either
 due to IN or due to not having the index's columns ordered optimally.  But
 they would be somewhat efficient due to "covering".

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