Opened 2 years ago
Last modified 13 months ago
#45354 new enhancement
Adding index for meta_key and post_id in postmeta
Reported by: |
|
Owned by: | |
---|---|---|---|
Milestone: | Awaiting Review | Priority: | normal |
Severity: | normal | Version: | |
Component: | Database | Keywords: | needs-testing dev-feedback |
Focuses: | performance | Cc: |
Description
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`);
Change History (4)
#2
in reply to:
↑ 1
@
2 years ago
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
#3
@
22 months ago
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.
@DuckDagobert when we have to call above sql query in core software?