[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:  reopened
 Priority:  normal        |   Milestone:  Future Release
Component:  Media         |     Version:  4.7
 Severity:  normal        |  Resolution:
 Keywords:  needs-patch   |     Focuses:  performance
--------------------------+-----------------------------

Comment (by Krstarica):

 On WordPress 5.7 media search is still unbearably slow on large websites.

 # Query_time: 12.417980  Lock_time: 0.000105  Rows_sent: 20
 Rows_examined: 942798
 {{{
 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%'
       )
       OR (
         wp_posts.post_excerpt LIKE '%test%'
       )
       OR (
         wp_posts.post_content LIKE '%test%'
       )
       OR (sq1.meta_value LIKE '%test%')
     )
   )
   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 '%test%' DESC,
   wp_posts.post_date DESC
 LIMIT
   0, 20;
 }}}


 EXPLAIN says:
 {{{
 +------+-------------+----------+------+------------------+------------------+---------+-------------------------+--------+---------------------------------------------------------------------+
 | id   | select_type | table    | type | possible_keys    | key
 | key_len | ref                     | rows   | Extra
 |
 +------+-------------+----------+------+------------------+------------------+---------+-------------------------+--------+---------------------------------------------------------------------+
 |    1 | SIMPLE      | wp_posts | ref  | type_status_date |
 type_status_date | 82      | const                   | 366787 | Using
 index condition; Using where; Using temporary; Using filesort |
 |    1 | SIMPLE      | sq1      | ref  | post_id,meta_key | post_id
 | 8       | wordpress.wp_posts.ID   |    1   | Using where
 |
 +------+-------------+----------+------+------------------+------------------+---------+-------------------------+--------+---------------------------------------------------------------------+
 }}}

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