CODEWITHSUNDEEP
mysql
user1917715
user1917715

Reputation: 9

MySQL long running query in 5.5 is very fast in 5.6

I have a complex database schema. I built a big query in MySQL 5.6 which joins 20 tables. When I run the query on a local copy of MySQL 5.6 on my Mac I am getting 1-2 millisecond response times. When I run the same query (with the same data via a mysql dump) on MySQL 5.5 on Ubuntu I am getting 60 second response times. I ran profiling on the slow query and discovered that almost the full 60 seconds is being spent in the 'statistics' stage. The 'statistics' stage is apparently where MySQL is determining how best to perform all the joins.

How can MySQL 5.6 perform this in milliseconds and in 5.5 it is taking a full minute?

Any ideas as to what to address first?

Upvotes: 0

Views: 900

Answers (2)

Michael McCloskey
Michael McCloskey

Reputation: 11

I found a solution myself after a few hours of hunting around. I simply added the clause STRAIGHT_JOIN right after my SELECT and magically my 60 second query on MySQL 5.5 is now down to 3 milliseconds.

So MySQL 5.6 had no problem with my large query but MySQL 5.5 did. So I guess the join optimizer was screwing up the table order.

Using STRAIGHT_JOIN forces MySQL to read the tables in the order they are specified in the query and bypasses the join optimizer.

Upvotes: 1

Federico Razzoli
Federico Razzoli

Reputation: 5351

MySQL 5.6 (and MariaDB) optimizer does a better job with subqueries and joins. I guess that your query is not well optimized in 5.5. Run:

EXPLAIN SELECT ...

on both MySQL versions, to understand what MySQL should do, and what really does in 5.5. Then, try to FORCE the use of the index used by 5.6.

EDIT: if it is a subquery, you can rewrite it as a join. MySQL 5.6 does this internally in some cases.

Upvotes: 4

Related Questions