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

Related tickets talking about the performance issues of this query: https://core.trac.wordpress.org/ticket/43658

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