[ad_1]
I’m getting a slow query on the frontend for logged in users with the myql query in the class-wt-smart-coupon-public.php#711 below:
The below query is taking 2.5s+ on a database with 2,000,000 rows (300mb) in postmeta. Using describe it’s showing pm1 scanning every row in postmeta. Decreasing the left joins or removing the orderby reduces the load time to milliseconds. Is it possible to optimize this? Possibly by removing the multiple left joins and filtering the postmeta in another query or with PHP.
SELECT p.ID
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS pm1 ON (p.ID = pm1.post_id AND pm1.meta_key = ‘_wt_make_auto_coupon’)
LEFT JOIN wp_postmeta AS pm2 ON (p.ID = pm2.post_id AND pm2.meta_key = ‘customer_email’)
LEFT JOIN wp_postmeta AS pm3 ON (p.ID = pm3.post_id AND pm3.meta_key = ‘usage_limit’)
LEFT JOIN wp_postmeta AS pm4 ON (p.ID = pm4.post_id AND pm4.meta_key = ‘usage_count’)
LEFT JOIN wp_postmeta AS pm5 ON (p.ID = pm5.post_id AND pm5.meta_key = ‘usage_limit_per_user’)
LEFT JOIN wp_postmeta AS pm6 ON (p.ID = pm6.post_id AND pm6.meta_key = ‘_wt_sc_user_roles’)
LEFT JOIN wp_postmeta AS pm7 ON (p.ID = pm7.post_id AND pm7.meta_key = ‘_wt_coupon_start_date’)
LEFT JOIN wp_postmeta AS pm8 ON (p.ID = pm8.post_id AND pm8.meta_key = ‘date_expires’)
LEFT JOIN wp_postmeta AS pm12 ON (p.ID = pm12.post_id AND pm12.meta_key = ‘coupon_amount’)
WHERE p.post_type = ‘shop_coupon’
AND p.post_status = ‘publish’
AND (pm1.meta_value =’1′ OR pm1.meta_value = ‘yes’)
AND (pm2.meta_value IS NULL OR pm2.meta_value = ” OR pm2.meta_value=”)
ORDER BY p.ID ASC LIMIT 0, 5;