Improve wp query perfomance on large db

Question

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

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.org/apis/handbook/transients/. The first query will still take time; but subsequent visitors can use the stored query result.