Adding index for meta_key and post_id in postmeta

[DuckDagobert]

We should add an index on post_id and meta_key as queries that contain these columns are incredibly common (even in loop in core on every page load basically).

An index will improve query speed up to 100% even when only 1000 posts are in db.

Test it yourself:

ALTER TABLE `wp_postmeta` ADD INDEX `wp_postmeta_key_id` (`meta_key`,`post_id`);

[mukesh27]

@DuckDagobert when we have to call above sql query in core software?

[DuckDagobert]

No, my code is the fix/patch for the problem.

For query example take the loop query (which gets post_id and _visibility for example).
But most WP core but also plugin queries like woocommerce,... will profit immensely from this index, as they select post_id inner join on postmeta where meta_key

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