[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 sboisvert):

 Since for most sites there are more /wp-admin/ pageloads than file uploads
 and the index solution is proving more problematic than expected I'd
 suggest we fix the problem with caching.

 Maybe something like this:
 wp-includes\media.php ~3255
 {{{#!php
 <?php
 $has_audio = get_transient( 'media_has_audio' );
 if ( false === $has_audio ){
         $has_audio = $wpdb->get_var( "
         SELECT ID
         FROM $wpdb->posts
         WHERE post_type = 'attachment'
         AND post_mime_type LIKE 'audio%'
         LIMIT 1
 " );
         set_transient( $has_audio, 'media_has_audio' );
 }

 $has_video = get_transient( 'media_has_video' );
 if ( false === $has_video ) {
         $has_video = $wpdb->get_var( "
                 SELECT ID
                 FROM $wpdb->posts
                 WHERE post_type = 'attachment'
                 AND post_mime_type LIKE 'video%'
                 LIMIT 1
         " );
         set_transient( $has_video, 'media_has_video' );
 }

 $months = get_transient( 'media_months_array' );
 if ( false === $months ) {
         $months = $wpdb->get_results( $wpdb->prepare( "
                 SELECT DISTINCT YEAR( post_date ) AS year, MONTH(
 post_date ) AS month
                 FROM $wpdb->posts
                 WHERE post_type = %s
                 ORDER BY post_date DESC
         ", 'attachment' ) );
         set_transient( $months, 'media_months_array' );
 }
 }}}
 Note that get_results and get_var return null when nothing is found, this
 means that we handle the not found case (which is often the slowest one
 for the has_* queries).

 We'd then need to add something like:
 {{{#!php
 <?php
 add_filter( 'wp_handle_upload', 'media_clear_caches', 10, 2 );
 function media_clear_caches( $file_array, $type ) {
         delete_transient( 'media_months_array' );
         if ( 'video' == $file_array['type'] ){
                 delete_transient( 'media_has_video' );
         } else if ( 'audio' == $file_array['type'] ) {
                 delete_transient( 'media_has_audio' );
         }
 }
 }}}
 Or just directly have the delete_transient inside _wp_handle_upload()

 If there's interest we can test the patch on our end.

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