[wp-trac] [WordPress Trac] #31071: post_mime_type related queries still slow on larger sites

[WordPress Trac]

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

 * keywords:  has-patch => has-patch dev-feedback


Comment:

 After chatting with @pento I worked on this today with @thomaswigley and
 we've improved the queries here. The patch I'm about to attach does the
 following:

 * Grabs a list of mime types
 * Filters that list for `audio*` and `video*`
 * Uses `IN` instead of `LIKE` for the query, along with the list of mime
 types

 In my testing on one of the sites affected by the slowness on
 WordPress.com the queries went from taking ~700ms down to 0.3ms.

 The `IN` portion of the query probably needs escaping - feedback
 appreciated. (`$wpdb->prepare`?)

 Note that the months query is still bad regardless but we should probably
 deal with that separately, and get these mime_type queries improved as a
 first step. (I'm happy to work on the months query also).

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