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.

Add a filter to ​get_available_post_mime_types().

[rcorrales]

Submitted a PR for adding a filter, similar to the one previously submitted, but with docs added and changing the $types variable name to $mime_types to avoid confusion with the $type variable, which is used for the post_type:

​https://github.com/WordPress/wordpress-develop/pull/5063

[johnbillion]

This looks good to go.

[johnbillion]

In 56452:

Media: Add a filter to the get_available_post_mime_types() function to allow overriding its database query.

This introduces the get_available_post_mime_types filter so this query can be skipped or cached by plugins.

Props maciejmackowiak, archon810, rcorrales

Fixes #52759

https://core.trac.wordpress.org/changeset/56452 👍

[emrikol]

Should this be a pre_* filter instead? Like pre_get_available_post_mime_types--I feel like other filter naming conventions follow similarly:

• pre_get_* short circuits if a non-null value is returned.
• get_* filters the results of the data from the function.

[johnbillion]

To be honest I would have preferred available_post_mime_types (without the get_ prefix) so it's more declarative, but a quick search showed that there's a large number of filters with a get_ prefix so I left it at that.

I guess it could be a pre_ filter but core is really inconsistent in this regard too. Re-opening in case anyone has a strong opinion; if not we can re-close.

[oglekler]

@spacedmonkey can you please investigate this from the performance point of view?

[joemcgill]

I agree that a pre_ filter name would be better here, since we're not filtering the return value from the DB, good suggestion @emrikol.

@oglekler there isn't a specific performance impact to adding the filter here, but it does create an opportunity for sites to potentially avoid an expensive DB query, which is why it makes sense as a performance enhancement.

Updating the filter name to pre_get_available_post_mime_types.

[rcorrales]

Thanks for the suggestion. I added a PR for updating the filter name to pre_get_available_post_mime_types.

[joemcgill]

The PR looks good to me. Going to leave for @johnbillion to approve, but marking for commit.

[joemcgill]

In 56623:

Media: Edit new hook name in get_available_post_mime_types().

This is a follow-up to [56452] in which a new filter hook was added to get_available_post_mime_types() to override a potentially slow query. This renames the previous hook from get_available_post_mime_types to pre_get_available_post_mime_types for clarity.

Props rcorrales, emrikol, johnbillion, joemcgill, mukesh27.
Fixes #52759.