0

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?

user1917715
  • 9
  • 1
  • 3
    Can you please provide us more info like what `Engine` do you use on both databases? or the specifications of your PC's? Maybe there alone you can find the reason. – Mark Apr 29 '13 at 07:56
  • 1
    Are the configurations of your mysql servers totally identical? – Jocelyn Apr 29 '13 at 09:13

2 Answers2

4

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.

Federico Razzoli
  • 4,901
  • 1
  • 19
  • 21
1

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.

Michael McCloskey
  • 11
  • 1