I have 100000 records in my database and so many meta query performed on it so it takes lot of time to perform the operation, what is the way to speed up this?

It takes 20 to 25s to load this query.

SELECT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1
ON ( wp_posts.ID = mt1.post_id )
INNER JOIN wp_postmeta AS mt2
ON ( wp_posts.ID = mt2.post_id )
WHERE 1=1
AND ( ( ( wp_postmeta.meta_key = 'profe'
AND wp_postmeta.meta_value = 'magic' )
AND ( mt1.meta_key = 'country'
AND mt1.meta_value IN ('uae','uk','usa') )
AND ( mt2.meta_key = 'chargesof' ) ) )
AND wp_posts.post_type = 'magicposts'
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY CAST(mt2.meta_value AS SIGNED) ASC
LIMIT 0, 12

I have 100000 records in my database and so many meta query performed on it so it takes lot of time to perform the operation, what is the way to speed up this?

It takes 20 to 25s to load this query.

SELECT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1
ON ( wp_posts.ID = mt1.post_id )
INNER JOIN wp_postmeta AS mt2
ON ( wp_posts.ID = mt2.post_id )
WHERE 1=1
AND ( ( ( wp_postmeta.meta_key = 'profe'
AND wp_postmeta.meta_value = 'magic' )
AND ( mt1.meta_key = 'country'
AND mt1.meta_value IN ('uae','uk','usa') )
AND ( mt2.meta_key = 'chargesof' ) ) )
AND wp_posts.post_type = 'magicposts'
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY CAST(mt2.meta_value AS SIGNED) ASC
LIMIT 0, 12

• wp-query
• mysql
• performance
• optimization

• Rick James and I have a relatively new plugin, Index WP MySQL for Speed, designed to address this kind of database performance problem. We're looking for backups or exports of large sites like yours we can use for load testing it. Are you willing to share your data? – O. Jones Commented Aug 6, 2021 at 10:45

1 Answer 1

The shop I'm looking after also has a lot of large queries and for us, the solution was a WP optimised hosting provider with litespeed caching and sufficient resources (RAM) to handle the query.

And for queries which are always the same, I also use transients. With transients, the query runs only once and is then stored until the transient expires. See https://developer.wordpress/apis/handbook/transients/. The first query will still take time; but subsequent visitors can use the stored query result.