3

When a user creates/updates a post within the admin, it takes between 15 and 30 seconds for that operation to complete.

The culprit appears to be this slow query:

SELECT ID
FROM wp_posts
WHERE post_type = 'attachment'
AND post_mime_type LIKE 'video%'
LIMIT 1

This is a known bug and the core team is working on it, however in the meantime, I'd like to be able to disable this query. Can I do this within my functions.php file using something like the pre_get_posts filter?

  • what is the aim of this query ? and where is it called ? – mmm Sep 1 '15 at 15:55
  • This query is found within the post page in the admin. I believe it's used for pre loading videos so when you click "add media" recent videos will be there. But our users don't upload videos to our admin, we use youtube and use their embed code so I just want to disable this query because it slows down the entire process. – bigmike7801 Sep 1 '15 at 15:58
4

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.

  • If I use 'audio%' it returns a SELECT false. However if I use '%audio%' it seems to work properly but displays as if I actually used 'audio%'. Does that make sense what I'm trying to say? – bigmike7801 Sep 1 '15 at 17:41
  • ...or maybe that was the point of it and I'm an idiot? – bigmike7801 Sep 1 '15 at 17:43
  • Here I'm targeting the hardcoded $has_audio and $has_video queries and use SELECT false instead so you can check if that makes any difference on the performance. @bigmike7801 – birgire Sep 1 '15 at 17:49
  • It does thank you. However, this may be an unrelated issue, but when I click "Update", it still takes 15-20 seconds to update the story. When I look at the network tab in my developer tools, it shows that the request to post.php is pending for quite some time. – bigmike7801 Sep 1 '15 at 17:58
  • 1
    I updated the answer so you can test the $months query with only the current month. @bigmike7801 – birgire Sep 1 '15 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.