Clash Royale CLAN TAG#URR8PPP

Speeding up WordPress query when searching for value in `meta_value`

Any tips on how I can speed up a WordPress query that searches for data in the meta_value in the postmeta table?

meta_value

postmeta

postmeta table contains just under 5 million records. A custom WP plug-in takes too long to process the data. I traced which query actually slows down and found this. The query is one of over 60 that is ran when get_posts is ran, bur this one takes 99% of the processing time.

postmeta

get_posts

SELECT wp_wordpress_posts.* FROM wp_wordpress_posts
INNER JOIN wp_wordpress_postmeta ON (wp_wordpress_posts.ID = wp_wordpress_postmeta.post_id)
INNER JOIN wp_wordpress_postmeta AS mt1 ON (wp_wordpress_posts.ID = mt1.post_id)
WHERE 1=1
AND wp_wordpress_posts.post_type = 'my_booking'
AND (wp_wordpress_posts.post_status = 'publish')
AND ( (wp_wordpress_postmeta.meta_key = '_rooms' AND CAST(wp_wordpress_postmeta.meta_value AS CHAR) = '236742')
AND (mt1.meta_key = '_bookingDate' AND CAST(mt1.meta_value AS CHAR) = '2018-09-18') )
GROUP BY wp_wordpress_posts.ID ORDER BY wp_wordpress_posts.post_date DESC


I started testing a simplified version

SELECT wp_wordpress_postmeta.* FROM wp_wordpress_postmeta
WHERE meta_key = '_rooms' AND CAST(meta_value AS CHAR) = '236742'


This query takes about a second to process. I realized that whoever wrote the plugin, stores searchable data in the meta_value field that is set to LONGTEXT so it can not be indexed. Since the searchable data is pretty short, I added shortened index to the meta_key and meta_value

meta_value

LONGTEXT

meta_key

meta_value

ALTER TABLE wp_wordpress_postmeta ADD key(meta_key(100), meta_value(100))

wp_wordpress_postmeta

I ran the short query and it improved the speed, making the query run 5 times faster. I tried the long query again, it it made no improvements to it. I tried running another query from the long one

SELECT wp_wordpress_postmeta.* FROM wp_wordpress_postmeta
WHERE meta_key = '_bookingDate' AND CAST(meta_value AS CHAR) = '2018-09-18'


This query takes about a second to process. Why does this query sees no improvement from the shortened index like the first one? How can I fix that?

mt1

explain

SELECT wp_wordpress_postmeta.* FROM wp_wordpress_postmeta WHERE meta_key = '_bookingDate' AND CAST(meta_value AS CHAR) = '2018-09-18'

1 Answer
1

Can you share few table records for wp_wordpress_posts

And wp_wordpress_postmeta table with table structure, it will help to analyse the actual problem.?

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.