My website's db is quite big. I have some 350,000 images in the media library (as well as some 350,000 posts). My wp_postmeta table has 4,580,552 rows.

As a result, some queries are insanely slow and I would like to somehow alter them. Starting with this query: Searching in the media library.

A search generates the following query:

SELECT wp_posts.ID
FROM wp_posts
LEFT JOIN wp_postmeta AS sq1
ON ( wp_posts.ID = sq1.post_id
AND sq1.meta_key = '_wp_attached_file' )
WHERE 1=1
AND (((wp_posts.post_title LIKE '%London%')
OR (wp_posts.post_excerpt LIKE '%London%')
OR (wp_posts.post_content LIKE '%London%')
OR (sq1.meta_value LIKE '%London%')))
AND wp_posts.post_type = 'attachment'
AND ((wp_posts.post_status = 'inherit'
OR wp_posts.post_status = 'private'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 20

Takes approximately 14 seconds. I'd like to disable the inclusion of searching in the fille name (which is redundent for us) - so that the query will not need wp_postmeta and look like this:

SELECT wp_posts.ID
FROM wp_posts
WHERE 1=1
AND (((wp_posts.post_title LIKE '%London%')
OR (wp_posts.post_excerpt LIKE '%London%')
OR (wp_posts.post_content LIKE '%London%')))
AND wp_posts.post_type = 'attachment'
AND ((wp_posts.post_status = 'inherit'
OR wp_posts.post_status = 'private'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 20

(This query, without wp_postmeta, takes 0.03 seconds).

Any advice how I can disable search of the attachment file names would be greatly appreciated.

My website's db is quite big. I have some 350,000 images in the media library (as well as some 350,000 posts). My wp_postmeta table has 4,580,552 rows.

As a result, some queries are insanely slow and I would like to somehow alter them. Starting with this query: Searching in the media library.

A search generates the following query:

SELECT wp_posts.ID
FROM wp_posts
LEFT JOIN wp_postmeta AS sq1
ON ( wp_posts.ID = sq1.post_id
AND sq1.meta_key = '_wp_attached_file' )
WHERE 1=1
AND (((wp_posts.post_title LIKE '%London%')
OR (wp_posts.post_excerpt LIKE '%London%')
OR (wp_posts.post_content LIKE '%London%')
OR (sq1.meta_value LIKE '%London%')))
AND wp_posts.post_type = 'attachment'
AND ((wp_posts.post_status = 'inherit'
OR wp_posts.post_status = 'private'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 20

Takes approximately 14 seconds. I'd like to disable the inclusion of searching in the fille name (which is redundent for us) - so that the query will not need wp_postmeta and look like this:

SELECT wp_posts.ID
FROM wp_posts
WHERE 1=1
AND (((wp_posts.post_title LIKE '%London%')
OR (wp_posts.post_excerpt LIKE '%London%')
OR (wp_posts.post_content LIKE '%London%')))
AND wp_posts.post_type = 'attachment'
AND ((wp_posts.post_status = 'inherit'
OR wp_posts.post_status = 'private'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 20

(This query, without wp_postmeta, takes 0.03 seconds).

Any advice how I can disable search of the attachment file names would be greatly appreciated.

• wp-query
• search
• media-library

• I've seen far larger sites with much faster performance that didn't require this kind of change. When diagnosing query performance in WP it's rarely useful to look at the direct SQL and instead much more informative and actionable to look at the API calls that lead to them ( afterall interfering with the SQL is a difficult task when a trivial parameter change could do the same thing ). More to the point if you approach this from the SQL side fo things it becomes 1000x harder to solve, you'd have much more luck inspecting which WP_Query's are created and their parameters – Tom J Nowell ♦ Commented Nov 11, 2023 at 15:04
• Thanks, Tom. This is a normal search in the backend on the media library, with no plugins or any alterations of the results display. – Biranit Goren Commented Nov 11, 2023 at 16:49

2 Answers 2

Approaching this from the SQL is a mistake, and instead a quick search of the official dev docs for filename reveals a filter named wp_allow_query_attachment_by_filename that is set to true by default:

https://developer.wordpress.org/reference/hooks/wp_allow_query_attachment_by_filename/

apply_filters( 'wp_allow_query_attachment_by_filename', bool $allow_query_attachment_by_filename )

Filters whether an attachment query should include filenames or not.

This means you can disable querying filenames with a quick filter like this:

add_filter( 'wp_allow_query_attachment_by_filename', '__return_false' );

Where __return_false is a function built into WordPress to save time writing filters like this.

OK, my colleague has found the solution:

add_filter( 'pre_get_posts', function($wp_query) {
      add_filter( 'wp_allow_query_attachment_by_filename', '__return_false', 999 );
    }, 999);

Works perfectly.

Thanks :)