Disable slow media queries?

Solution for WordPress versions >= 4.7.4 (4.8)

Ticket #31071 introduces patches with new filters to override three possible slow media queries, in the wp_enqueue_media() function:

• media_library_show_audio_playlist (@param bool|null)

  From the inline doc: Whether to show the button, or null to decide based on whether any audio files exist in the media library.

• media_library_show_video_playlist (@param bool|null)

  From the inline doc: Whether to show the button, or null to decide based on whether any video files exist in the media library.

• media_library_months_with_files (@param array|null)

  From the inline doc: An array of objects with month and year properties, or null (or any other non-array value) for default behavior.

Example:

Here’s a demo plugin:

<?php
/**
  * Plugin Name:  Override Possible Slow Media Queries
  * Plugin URI:   https://wordpress.stackexchange.com/a/200383/26350
  */

// Always show audio button
add_filter( 'media_library_show_audio_playlist', '__return_true' );

// Always show video button
add_filter( 'media_library_show_video_playlist', '__return_true' );

// Cache media library file months with the transients API
add_filter( 'media_library_months_with_files', function( $months )
{
    // Generate file months when it's not cached or the transient has expired
    if ( false === ( $months = get_transient( 'wpse_media_library_months_with_files' ) ) )
    {
        global $wpdb;

        /**
         * Note that we want to avoid returning non-array file months,  
         * to avoid running the slow query twice.
         *
         * From the Codex for wpdb::get_results( $query, $output_type ):
         *
         * "If no matching rows are found, or if there is a 
         *  database error, the return value will be an empty array.
         *  If your $query string is empty, or you pass an invalid 
         *  $output_type, NULL will be returned."
         *
         * So it looks like we're covered, as we're not dealing with 
         * empty query or a wrong return type.
         */
        $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' ) );

        // Cache the results
        set_transient(
            'wpse_media_library_months_with_files',
                $months,
                12 * HOUR_IN_SECONDS   // <-- Override to your needs!
             );
    }
    return $months;
} );

Note that we could also handpick the file months with e.g.:

$months = [
    (object) [ 'year' => 2017, 'month' => 2 ],
    (object) [ 'year' => 2017, 'month' => 1 ],
    (object) [ 'year' => 2016, 'month' => 12 ],
];

using the media_library_months_with_files filter.

Previous answer

These queries are in the wp_enqueue_media() function:

$has_audio = $wpdb->get_var( "
            SELECT ID
            FROM $wpdb->posts
            WHERE post_type="attachment"
            AND post_mime_type LIKE 'audio%'
            LIMIT 1
    " );
 $has_video = $wpdb->get_var( "
            SELECT ID
            FROM $wpdb->posts
            WHERE post_type="attachment"
            AND post_mime_type LIKE 'video%'
            LIMIT 1
    " );
 $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' ) );

Here’s one way to modify these potential slow queries:

/**
 * Modify the potential slow $has_audio, $has_video and $months queries
 *
 * @link http://wordpress.stackexchange.com/a/200383/26350
 */
add_filter( 'media_upload_tabs', function( $tabs )
{
    add_filter( 'query', 'wpse_replace_months_sql' );
    add_filter( 'query', 'wpse_replace_audio_video_sql' );
    return $tabs;
} );

add_filter( 'media_view_settings', function( $settings )
{
    remove_filter( 'query', 'wpse_replace_months_sql' );
    remove_filter( 'query', 'wpse_replace_audio_video_sql' );
    return $settings;
} );

where (PHP 5.4+):

/**
 * Use "SELECT false" for the $has_audio and $has_video queries
 */
function wpse_replace_audio_video_sql( $sql )
{
   global $wpdb;
   foreach( [ 'audio', 'video' ] as $type )
   {
      $find = "SELECT ID FROM {$wpdb->posts} WHERE post_type="attachment" 
          AND post_mime_type LIKE '{$type}%' LIMIT 1";
      if( trim( preg_replace('/\s+/', ' ', $sql) ) == trim( preg_replace('/\s+/', ' ', $find) ) )
          return "SELECT false"; // <-- We could also use true here if needed
   }
   return $sql;
} 

and

/**
 * Replace the available months query with the current month
 */
function wpse_replace_months_sql( $sql )
{
    global $wpdb;
    $find = "SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month
        FROM {$wpdb->posts} WHERE post_type="attachment" ORDER BY post_date DESC";
    if( trim( preg_replace('/\s+/', ' ', $sql) ) == trim( preg_replace('/\s+/', ' ', $find) ) )
         $sql = "SELECT YEAR( CURDATE() ) as year, MONTH( CURDATE() ) as month";
    return $sql;
}

We might try to refine this by creating an has_audio and has_video counters in the option table and update it whenever we upload/delete an audio or video file.

In the trac ticket, mentioned in the question, there’s a proposed index:

ALTER TABLE $wpdb->posts ADD INDEX type_mime(post_type,post_mime_type)

that might give some boost.

@Denis-de-Bernardy also gives an example of alternative queries for the months part.