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

WordPress Trac noreply at wordpress.org
Mon Mar 28 17:18:09 UTC 2016

#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

 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?

 '''Query Performance Spreadsheet:'''

 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

More information about the wp-trac mailing list