WordPress.org

Make WordPress Core

Opened 11 months ago

Last modified 7 months ago

#45354 new enhancement

Adding index for meta_key and post_id in postmeta

Reported by: DuckDagobert Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version:
Component: Database Keywords: needs-testing dev-feedback
Focuses: performance Cc:
PR Number:

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 (3)

#1 follow-up: @mukesh27
11 months ago

  • Focuses performance added
  • Keywords needs-testing dev-feedback added

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

#2 in reply to: ↑ 1 @DuckDagobert
11 months 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 @fliespl
7 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.

Note: See TracTickets for help on using tickets.