Opened 5 years ago
Last modified 21 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 (5)
#2
in reply to:
↑ 1
@
5 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
@
4 years 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.
#5
@
21 months ago
I'd say an index like this is beneficial if it helps frontend visitors, or if it helps backend users without causing any frontend delays. With the wide variety of plugins that use post meta, this may be difficult to determine, so maybe site admins should consider this on a site-by-site basis.
@fliespl @DuckDagobert Have you tested both "post_id, meta_key" and "meta_key, post_id" to see which one is faster? These columns vary in cardinality and speed of comparison, so one may be better than the other.
Also, have you measured the impact on both frontend and backend? Can you share more contextual details?
@DuckDagobert when we have to call above sql query in core software?