Subject: Re: [wp-trac] [WordPress Trac] #39358: Media
search speed has been dramatically reduced



#39358: Media search speed has been dramatically reduced
--------------------------+------------------------------
Reporter: merts | Owner: joemcgill
Type: defect (bug) | Status: reviewing
Priority: normal | Milestone: Awaiting Review
Component: Media | Version: 4.7
Severity: normal | Resolution:
Keywords: | Focuses: performance
--------------------------+------------------------------
Changes (by dd32):

* keywords: 2nd-opinion =>
* focuses: => performance


Comment:

The issue we have here, is quite simple - You cannot search postmeta on
large sites, there's no way around that, it's just not possible to do it
in a performant way.

The user experience gained by this change is good however, it works as an
end-user may intend the search to.

IMHO there's a few options of varying usefulness
a) Drop it entirely
b) Drop it for "large sites" (ie. more than x number of post) - The UX of
this is bad
c) Limit it to exact matches only for the postmeta, which with the help of
a new DB index would make it much faster.
d) Store the attachment data in another way.

For reference, this is what the SQL ends up looking like:
{{{
SELECT SQL_CALC_FOUND_ROWS 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 '%test.diff%' )
OR ( wp_posts.post_excerpt LIKE '%test.diff%' )
OR ( wp_posts.post_content LIKE '%test.diff%' )
OR ( sq1.meta_value LIKE '%test.diff%' ) ))
AND wp_posts.post_type = 'attachment'
AND ( wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'future'
OR wp_posts.post_status = 'draft'
OR wp_posts.post_status = 'pending'
OR wp_posts.post_status = 'private' )
GROUP BY wp_posts.id
ORDER BY wp_posts.post_title LIKE '%test.diff%' DESC,
wp_posts.post_date DESC
LIMIT 0, 10
}}}

--
Ticket URL: <https://core.trac.wordpress.org/ticket/39358#comment:7>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform
_______________________________________________
wp-trac mailing list
[email protected]
http://lists.wordpress.org/mailman/listinfo/wp-trac



Programming list archiving by: Enterprise Git Hosting