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

[maciejmackowiak]

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

[archon810]

+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.

https://core.trac.wordpress.org/ticket/52759

[maciejmackowiak]

Here is a pull request:
​https://github.com/WordPress/wordpress-develop/pull/1088/files

[Mista-Flo]

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)

[archon810]

Great idea 💡.

[maciejmackowiak]

check the slack thread we started for further comments

Can you point me to that thread, please?

[archon810]

@maciejmackowiak The link is right above. ​https://wordpress.slack.com/archives/core-media/p1615475295157700

[priard]

Replying to archon810:

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

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

Ok, let's say a new filter is added, what next? How would you like to improve the query for better performance?

Drop an example of such a query. I am curious about your experience with this.