Postmeta - Performance Improvement - Woocommerce

[kapil4sam]

Hi Team,

Struggling since almost a month to find the solution for slow search in woocoomerce. Tried verious different things like speed up index mysql. Changing mysql version from 5.7 to 8.x.
Updated index - added high performance keys for post meta table.

When backend team search for orders from woocommerce-orders tab - it takes almost 3 min to get the results.

Looking for inputs from wordpress community and help for the same.

[kapil4sam]

SELECT DISTINCT p1.post_id
FROM wpuo_postmeta p1
WHERE p1.meta_value LIKE '%123456%'
AND p1.meta_key IN ('_billing_address_index','_shipping_address_index','_billing_last_name','_billing_email','_order_number')

Above is the query which is taking too much of time.

[pravinparmar2404]

Attached Slow Queries screenshot from Query Monitor

​https://prnt.sc/SyEJa0syyTnw

[uzumymw]

Usually DISTINCT leads to a big slowdown.