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

[WordPress Trac]

#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