[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
--------------------------+------------------------------

Comment (by dd32):

 > c) Limit it to exact matches only for the postmeta, which with the help
 of a new DB index would make it much faster.

 To follow that up, it'd actually have to be done with the help of a new
 meta key.. The reason being, is that the meta values are currently stored
 like so: `2015/01/G6JgkNp.jpg` OR `/home/someuser/public_html/wp-
 content/uploads/oj0KHQm.jpg`. Exact-matching and/or indexing isn't going
 to help here without a rethink of where the filename is available.

 We could use the fact that the `post_name` field is going contain the
 basename of the filename, ie. `g6jgknp` in the case of `G6JgkNp.jpg`,
 combined with the fact that the Guid field ''currently'' contains the
 filename. That would at least cause it to hit indexes and not require a
 table join, for example (note the 4th search OR in both the previous and
 this example)

 {{{
 SELECT SQL_CALC_FOUND_ROWS wp_posts.id
 FROM   wp_posts

 WHERE  1 = 1
        AND (( ( wp_posts.post_title LIKE '%08W0zJv.jpg%' )
                OR ( wp_posts.post_excerpt LIKE '%08W0zJv.jpg%' )
                OR ( wp_posts.post_content LIKE '%08W0zJv.jpg%' )
                OR ( wp_posts.post_name = '08W0zjv' AND wp_posts.guid LIKE
 '%08W0zJv.jpg%' )
         ))
        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_title LIKE '%08W0zJv.jpg%' DESC,
           wp_posts.post_date DESC
 LIMIT  0, 10
 }}}

--
Ticket URL: <https://core.trac.wordpress.org/ticket/39358#comment:9>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform