WordPress版本>=4.7.4（4.8）的解决方案#31071 引入了带有新过滤器的修补程序，以覆盖三种可能的慢速媒体查询wp_enqueue_media() 功能：

• media_library_show_audio_playlist （@参数布尔|空）

  从内联文档：是否显示按钮，或null 根据媒体库中是否存在任何音频文件来决定

• media_library_show_video_playlist （@参数布尔|空）

  从内联文档：是否显示按钮，或null 根据媒体库中是否存在任何视频文件来决定

• media_library_months_with_files （@参数数组| null）

  从内联文档：具有month 和year 属性，或null （或任何其他非数组值）的默认行为

Example:

下面是一个演示插件：

<?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;
} );

请注意，我们还可以手工选择文件月份，例如：

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

使用media_library_months_with_files 滤器

上一个答案

这些查询位于wp_enqueue_media() 功能：

$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\' ) );

有一种方法可以修改这些潜在的慢速查询：

/**
 * 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;
} );

其中（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;
} 

以及

/**
 * 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;
}

我们可以通过创建has_audio 和has_video 计数器，并在上载/删除音频或视频文件时对其进行更新。

在问题中提到的trac记录单中proposed index:

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

这可能会带来一些推动。

@Denis de Bernardy还提供了月份部分的备选查询示例。