[wp-trac] [WordPress Trac] #31071: media / post_mime_type related queries are very 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
-------------------------------------------------+-------------------------

Comment (by jamesmehorter):

 I tested these 3 queries on a table with 1.1m rows in MySQL 5.0.95,
 5.5.31, and 5.6.17 (The versions @archon810 was using) with and without
 the new post_type/mime_type index. Those tests are documented here: (and
 attached to this ticket below) https://docs.google.com/spreadsheets/d
 /1F45r8niIqw5S7XHLA-i2M1nTzuO592QtyOO6HcEgUFs/edit?usp=sharing

 However, the months/years query uses the type_status_date index
 regardless—The incorrect index usage @archon810 noticed in [#comment:9
 (comment 9)] is not reproducible for me. @archon810 any chance that was a
 mistake? Mind sharing the data you're using?

 It appears the months/years query is slow regardless of the new index—and
 unless @archon810 is able to provide reproducible steps I'm thinking we
 should ignore that query and proceed with the new index. As such, I've
 also attached a patch which simply adds the new index. Thoughts?

 '''Patch:'''
 https://core.trac.wordpress.org/attachment/ticket/31071/31071-d.diff
 '''Query Performance Spreadsheet:'''
 https://core.trac.wordpress.org/attachment/ticket/31071/WP%20Core%20Trac%20%2331071%20MySQL%20Queries.xlsx

 I also like @sboisvert's idea. Though, for it to be cacheable each query
 would also need an action after the queries execute to cache the
 results—so the results may be supplied back to those new filters.

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