[wp-trac] [WordPress Trac] #31071: media / post_mime_type related queries are very slow on larger sites (was: post_mime_type related queries still slow on larger sites)

[WordPress Trac]

#31071: media / post_mime_type related queries are very slow on larger sites
-------------------------------------------------+-------------------------
 Reporter:  archon810                            |       Owner:  pento
     Type:  defect (bug)                         |      Status:  assigned
 Priority:  normal                               |   Milestone:  Future
Component:  Media                                |  Release
 Severity:  normal                               |     Version:  4.1
 Keywords:  dev-feedback reporter-feedback 2nd-  |  Resolution:
  opinion has-patch                              |     Focuses:
                                                 |  performance
-------------------------------------------------+-------------------------
Changes (by tha_sun):

 * keywords:  dev-feedback reporter-feedback 2nd-opinion => dev-feedback
     reporter-feedback 2nd-opinion has-patch


Comment:

 This issue causes two slow queries with a duration of 12 seconds (each) on
 one of our latest sites currently, on every load of the administrative
 post add/edit page.  The site merely has 40k posts.

 I agree that a multi-column index is not the right solution, because
 column order matters (a lot).  That's why the other mentioned query
 immediately cropped up.

 Replacing the LIKE with discrete values does not help either, because
 there is no index for the column, so the table scan happens anyway.
 That's expected.

 Caching results in individual transients is a terrible idea for use-cases
 like `wp_enqueue_media()`, because caching application state (affecting
 the user interface) in a magic transient adds a complex layer of
 abstraction, for which WordPress core and especially the vast majority of
 contrib plugins and themes do not seem to be prepared at this point in
 time.

 I agree on 1) we simply need an index.  But the index can and should be
 optimized in size for its primary use-case by limiting its length.

 We don't need to modify the query as suggested in 2) because in the rare
 case the MySQL query optimizer chooses a wrong index, there's a native
 solution for that: [http://dev.mysql.com/doc/refman/5.0/en/index-
 hints.html index hints], as already demonstrated in #comment:1.

 And we don't want 3) for reasons explained above.

 Therefore, attached patch does the following:

 1. Adds an index to `wp_posts` for `post_type, post_mime_type`.
 1. Increases `$wp_db_version`, so the schema change is picked up by
 `dbDelta()`.
 1. Instructs MySQL's query optimizer to `IGNORE INDEX
 (post_type_mime_type)`.  Not explicitly suggesting the old one, because
 it's not the most ideal and a custom index might have been added, which
 MySQL should still choose.  Thus, we just ignore this new index.

 Any chance to move forward with this?

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