Make WordPress Core

Opened 2 years ago

Last modified 12 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's profile 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 (10)

#1 @johnbillion
2 years ago

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

#2 follow-up: @archon810
2 years 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.


2 years ago
#3

  • Keywords has-patch added; needs-patch removed

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


2 years ago

#6 @Mista-Flo
2 years 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).

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

Last edited 2 years ago by Mista-Flo (previous) (diff)

#7 @archon810
2 years ago

Great idea 💡.

#8 @maciejmackowiak
2 years ago

check the slack thread we started for further comments

Can you point me to that thread, please?

#10 in reply to: ↑ 2 @priard
12 months ago

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.

Note: See TracTickets for help on using tickets.