I have a RDS server (rd3.medium) on AWS that is running basic configuration. I'm trying to run this query, which runs in a few milliseconds on my local computer, as well as a basic web server where I have MySQL server installed. However, when I run it on RDS it takes 20 seconds. I'm using General Provisioned SSD's for storage.
SELECT DISTINCT
p2.id
FROM
wp_posts p2
INNER JOIN
wp_postmeta pm2
ON
pm2.meta_key = 'wpcr3_review_post'
AND
pm2.post_id = p2.id
INNER JOIN
wp_posts p3
ON
p3.id = pm2.meta_value
AND
p3.post_status = 'publish'
INNER JOIN
wp_postmeta pm3
ON
pm3.meta_key = 'wpcr3_enable'
AND
pm3.meta_value = '1'
WHERE
p2.post_type = 'wpcr3_review'
I did notice when I ran EXPLAIN
that it uses a temporary table, and so I wonder if there's some way in which RDS doesn't handle that well, while a basic MySQL server install does?
1 SIMPLE pm3 ref meta_key meta_key 576 const 334 Using where; Using temporary
1 SIMPLE pm2 ref post_id,meta_key meta_key 576 const 1335 Using where
1 SIMPLE p2 eq_ref PRIMARY,type_status_date PRIMARY 8 deaddictioncentres_staging.pm2.post_id 1 Using where
1 SIMPLE p3 eq_ref PRIMARY PRIMARY 8 deaddictioncentres_staging.pm2.meta_value 1 **Using index condition;** Using where; Distinct
The only difference that I find on all other servers is the RDS server adds the Using index condition; where none of the others do.
Update I found out the difference of Using index condition; was due to moving from 5.5 to 5.6. But I can't understand why moving from 5.5 to 5.6 would make things so much slower.