RDS Performance slow compared to local mysql install

Question

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.

Do the versions of MySQL differ between your local machine and you RDS instance? Does the amount of data differ? — jordanm, May 25 '16 at 03:13
Version is 5.6 on RDS and 5.5 on my server, but I struggle to see how upgrading to 5.6 could make things so much slower on a relatively standard query. — user814584, May 25 '16 at 06:28

score 1 · Answer 1 · answered May 25 '16 at 14:08

So I figured out the answer. The problem was due to the bad query design. (This query was coming from a Wordpress Plugin, so it's not surprising). The third join was not only worthless, it was attaching "creating" 1 record in the result set for every record in the Post Meta table that had wpcr3_enable set to 1 * every record in the WP Post table, resulting in 500,000 rows. I believe the speed on local was just an issue with the sending/processing of that data. The added DISTINCT keyword was just covering up the issue, and is actually unnecessary in a properly coded query.

RDS Performance slow compared to local mysql install

1 Answers1