# Query has drastic slowdown following upgrade from MySQL 5.6 to 5.7

**URL:** <https://forums.percona.com/t/query-has-drastic-slowdown-following-upgrade-from-mysql-5-6-to-5-7/6985>\
**Category:** Percona Server for MySQL 5.7\
**Created:** [April 25, 2019, 7:02pm UTC](https://forums.percona.com/t/query-has-drastic-slowdown-following-upgrade-from-mysql-5-6-to-5-7/6985 "2019-04-25T19:02:09Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![kflavin](https://avatars.discourse-cdn.com/v4/letter/k/ecd19e/32.png) [@kflavin](https://forums.percona.com/u/kflavin)\
**Post date:** [April 25, 2019, 7:02pm UTC](https://forums.percona.com/t/query-has-drastic-slowdown-following-upgrade-from-mysql-5-6-to-5-7/6985/1 "2019-04-25T19:02:09Z")

</div>

Following an upgrade from MySQL 5.6 to 5.7, the following query went from running in \<2 minutes to taking over 30 minutes.  
SELECT DISTINCT Name,d.id,deviceType,issuedBy, description,avNum,CompanyName, BrandName,dwNumber,quant,discDate,Type FROM table\_one d JOIN table\_two i ON d.id = i.id;  
I have tried a variety of things to diagnose the issue.

- I compared optimizer\_switch settings between 5.6 and 5.7, and switched “off” the new settings:  
SET optimizer\_switch=‘condition\_fanout\_filter=off’; SET optimizer\_switch=‘derived\_merge=off’; SET optimizer\_switch=‘duplicateweedout=off’;

- I compared configuration/variable settings between the 5.6 and 5.7 servers. Where applicable, I made the 5.7 settings match 5.6. I made quite a few changes to variables (more than I can list here) and none seemed to have much of an impact, better or worse.

- I enabled the performance\_schema tables and compared results.

The 5.7 instance shows many calls/time spent in something called “wait/synch/sxlock/innodb/hash\_table\_locks”. This led me to look more closely at InnoDB and storage.

- The two servers are in AWS RDS. As a comparison, I started two identical EC2 instances. One with MariaDB 10.2.21 (InnoDB version 5.7.24), and one with MySQL 5.7.25 (InnoDB version 5.7.25) using out-of-the-box config.

The 5.7 server still takes 30 minutes in this configuration, but like our 5.6 server, MariaDB took only 60 seconds! This makes me think 1) it’s not a storage contention issue, and 2) InnoDB doesn’t appear to be a limiting factor, because it works fine within MariaDB.

My thinking is it must be something different in the query execution plan chosen by the 5.7 server. I’m having trouble verifying this though.

The EXPLAINs for both are pretty similar. MariaDB has a “using where” and reverses the order of the tables. However, when I force the same table order in the 5.7 query (using STRAIGHT\_JOIN), it doesn’t get any faster.

MariaDB: [URL][https://pastebin.com/Ma8tLQrm[/URL]](https://pastebin.com/Ma8tLQrm%5B/URL%5D)  
MySQL 5.7 (and 5.6): [URL][https://pastebin.com/S4QELM3c[/URL]](https://pastebin.com/S4QELM3c%5B/URL%5D)

The 5.6 EXPLAIN is identical to the 5.7 EXPLAIN, even though they don’t behave in the same way.

Apologies for posting an external link, but I’ve gathered some more data, including my table definitions, here: [URL][MySQL join has drastically worse performance after upgrading from 5.6 to 5.7 - Database Administrators Stack Exchange](https://dba.stackexchange.com/questions/235351/mysql-join-has-drastically-worse-performance-after-upgrading-from-5-6-to-5-7%5B/URL%5D)

Does anyone know what might be going on? I’ve been stuck on this for nearly a week, and I don’t think diving into the source is going to be a productive exercise for me, so I’m blocked. I’m really interested to understand what is going on here. The performance hit is quite significant for such a straightforward query.