WordPress.org

Make WordPress Core

Opened 9 months ago

Last modified 9 months ago

#52759 new feature request

Add a filter to get_available_post_mime_types() function to allow overriding extremely slow query.

Reported by: maciejmackowiak Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version:
Component: Media Keywords: good-first-bug has-patch
Focuses: performance Cc:

Description

On sites sites with lot of posts the query used in get_available_post_mime_types is extremely slow.
It would be nice to have a filter that will allow us to override this query and for example cache the results.

It could be something simillar to this filter:
https://core.trac.wordpress.org/browser/tags/5.6/src/wp-includes/media.php#L4172

<?php
function get_available_post_mime_types($type = 'attachment') {
        global $wpdb;
        $types = apply_filters('get_available_post_mime_types', null, $type);
        if ( ! is_array( $types ) ) {
                $types = $wpdb->get_col($wpdb->prepare( "SELECT DISTINCT post_mime_type FROM $wpdb->posts WHERE post_type = %s", $type ) );
        }
        return $types;
}

Change History (9)

#1 @johnbillion
9 months ago

  • Component changed from General to Media
  • Focuses performance added
  • Keywords needs-patch good-first-bug added

#2 @archon810
9 months ago

+1 for this.

For us, it adds an additional 0.35s or so (right now, more if db is loaded).

SELECT SQL_NO_CACHE DISTINCT post_mime_type
FROM wp_posts
WHERE post_type = 'attachment'
> OK
> Time: 0.35s

On the grid view it is called from here:
https://github.com/WordPress/WordPress/blob/785cb6cc7d4d17ef0aa16b187dcfb6a8dc513e1b/wp-admin/includes/class-wp-media-list-table.php#L97

It calls this function:
https://github.com/WordPress/WordPress/blob/00680f2e89766e4ac9a71c68a8cccd3a141afb34/wp-admin/includes/post.php#L1287

And here is the query:
https://github.com/WordPress/WordPress/blob/bf83c368fdfcee2b879d00b193f505038e1681f0/wp-includes/post.php#L7517

We were able to cache and greatly speed up this query already:

SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month
FROM wp_posts
WHERE post_type = 'attachment'
AND post_status != 'auto-draft'
AND post_status != 'trash'
ORDER BY post_date DESC

and I hope with the new proposed filter, we will be able to speed up the site even more.

This ticket was mentioned in PR #1088 on WordPress/wordpress-develop by maciejmackowiak.


9 months ago

  • Keywords has-patch added; needs-patch removed

This ticket was mentioned in Slack in #core-media by antpb. View the logs.


9 months ago

#6 @Mista-Flo
9 months ago

Hi there,

I'm definitely for a filter to short circuit this slow query, but I think first we need to fix this slow query by implementing a cache, wether it use the internal cache API system from Core or the Transient API, (check the slack thread we started for further comments)

Version 0, edited 9 months ago by Mista-Flo (next)

#7 @archon810
9 months ago

Great idea 💡.

#8 @maciejmackowiak
9 months ago

check the slack thread we started for further comments

Can you point me to that thread, please?

Note: See TracTickets for help on using tickets.