mysql 5.6比5.5快,MySQL在5.5中长期运行的查询在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?

解决方案

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.