Removing filename searches when searching attachments

Question

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.

Answer 1

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.

Answer 2

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