0

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.

2
  • 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
    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. Nov 11, 2023 at 16:49

2 Answers 2

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.

5
  • I tried it. Unfortunately it has no effect because WordPress turns it to true in these lines: ./wp-admin/includes/ajax-actions.php:3055: add_filter( 'wp_allow_query_attachment_by_filename', '__return_true' ); ./wp-admin/includes/post.php:1349: add_filter( 'wp_allow_query_attachment_by_filename', '__return_true' ); ./wp-includes/rest-api/endpoints/class-wp-rest-attachments-controller.php:100: add_filter( 'wp_allow_query_attachment_by_filename', '__return_true' ); Nov 11, 2023 at 16:47
  • that's just a matter of changing the priority used when calling add_filter, it defaults to 10 so go higher
    – Tom J Nowell
    Nov 11, 2023 at 18:00
  • Fraid that doesn't work either. Tried puttig this in functions.php: add_filter( 'wp_allow_query_attachment_by_filename', '__return_false', 99 ); and it does not apply Nov 11, 2023 at 19:21
  • assuming it is in the active theme and at the top level of the file it should have worked, try higher priorities
    – Tom J Nowell
    Nov 12, 2023 at 12:12
  • I put it as the first command in the file functions.php of the active theme with priority 99999 - nada. Makes no difference :/ Nov 12, 2023 at 19:53
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.