[wp-trac] [WordPress Trac] #45354: Adding index for meta_key and post_id in postmeta

[WordPress Trac]

#45354: Adding index for meta_key and post_id in postmeta
----------------------------------------+------------------------------
 Reporter:  DuckDagobert                |       Owner:  (none)
     Type:  enhancement                 |      Status:  new
 Priority:  normal                      |   Milestone:  Awaiting Review
Component:  Database                    |     Version:
 Severity:  normal                      |  Resolution:
 Keywords:  needs-testing dev-feedback  |     Focuses:  performance
----------------------------------------+------------------------------

Comment (by fliespl):

 I can confirm that adding such might improve performance on many plugins.

 Let's take imagify for example (with 100000 attachment in database).

 Query:

 {{{
 SELECT SQL_NO_CACHE count(1)
 FROM wp_posts AS p
 INNER JOIN wp_postmeta AS imrwpmt1
 ON ( p.ID = imrwpmt1.post_id
 AND imrwpmt1.meta_key = '_wp_attached_file' )
 INNER JOIN wp_postmeta AS imrwpmt2
 ON ( p.ID = imrwpmt2.post_id
 AND imrwpmt2.meta_key = '_wp_attachment_metadata' )
 INNER JOIN wp_postmeta AS mt1
 ON ( p.ID = mt1.post_id
 AND mt1.meta_key = '_imagify_status' )
 WHERE p.post_mime_type IN (
 'image/jpeg','image/png','image/gif','application/pdf' )
 AND p.post_type = 'attachment'
 AND p.post_status IN ( 'inherit','private' )
 AND mt1.meta_value IN ( 'success', 'already_optimized' )
 AND imrwpmt1.meta_value NOT LIKE '%://%'
 AND imrwpmt1.meta_value NOT LIKE '_:\\\%'
 AND ( LOWER( imrwpmt1.meta_value ) LIKE '%.jpg'
 OR LOWER( imrwpmt1.meta_value ) LIKE '%.jpeg'
 OR LOWER( imrwpmt1.meta_value ) LIKE '%.jpe'
 OR LOWER( imrwpmt1.meta_value ) LIKE '%.png'
 OR LOWER( imrwpmt1.meta_value ) LIKE '%.gif'
 OR LOWER( imrwpmt1.meta_value ) LIKE '%.pdf' );
 }}}


 a) default wordpress indexes:
 **1 row in set (28.22 sec)**

 b) after adding post_id + meta_key index
 {{{
 ALTER TABLE wp_postmeta ADD INDEX( `post_id`, `meta_key`);
 }}}

 **1 row in set (2.51 sec)**


 It could be 10 times faster in some situations.

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