I have a MySQL database with InnoDB tables summing up over 10 ten GB of data that I want to migrate from MySQL 5.5 to MySQL 5.7. And I have a query that looks a bit like:
SELECT dates.date, count(mySub2.myColumn1), sum(mySub2.myColumn2)
FROM (
SELECT date
FROM dates -- just a table containing all possible dates next 5 years
WHERE date BETWEEN '2016-06-01' AND '2016-09-03'
) AS dates
LEFT JOIN (
SELECT o.id, time_start, time_end
FROM order AS o
INNER JOIN order_items AS oi on oi.order_id = o.id
WHERE time_start BETWEEN '2016-06-01' AND '2016-09-03'
) AS mySub1 ON dates.date >= mySub1.time_start AND dates.date < mySub1.time_end
LEFT JOIN (
SELECT o.id, time_start, time_end
FROM order AS o
INNER JOIN order_items AS oi on oi.order_id = o.id
WHERE o.shop_id = 50 AND time_start BETWEEN '2016-06-01' AND '2016-09-03'
) AS mySub2 ON dates.date >= mySub2.time_start AND dates.date < mySub2.time_end
GROUP BY dates.date;
My problem is that this query is performing fast in MySQL 5.5 but extremely slow in MySQL 5.7.
In MySQL 5.5 it is taking over 1 second at first and < 0.001 seconds every recurring execution without restarting MySQL.
In MySQL 5.7 it is taking over 11.5 seconds at first and 1.4 seconds every recurring execution without restarting MySQL.
And the more LEFT JOINs I add to the query, the slower the query becomes in MySQL 5.7.
Both instances now run on the same machine, on the same hard drive and with the same my.ini settings. So it isn't hardware.
The execution plans do differ, though and I don't know what to make from it.
This is the EXPLAIN EXTENDED on MySQL 5.5:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | extra |
|----|-------------|------------|-------|---------------|-------------|---------|-----------|-------|----------|---------------------------------|
| 1 | PRIMARY | dates | ALL | | | | | 95 | 100.00 | Using temporary; Using filesort |
| 1 | PRIMARY | <derived2> | ALL | | | | | 281 | 100.00 | '' |
| 1 | PRIMARY | <derived3> | ALL | | | | | 100 | 100.00 | '' |
| 3 | DERIVED | o | ref | xxxxxx | shop_id_fk | 4 | '' | 1736 | 100.00 | '' |
| 3 | DERIVED | oc | ref | xxxxx | order_id_fk | 4 | myDb.o.id | 1 | 100.00 | Using index |
| 2 | DERIVED | o | range | xxxx | date_start | 3 | | 17938 | 100.00 | Using where |
| 2 | DERIVED | oc | ref | xxx | order_id_fk | 4 | myDb.o.id | 1 | 100.00 | Using where |
This is the EXPLAIN EXTENDED on MySQL 5.7:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | extra |
|----|-------------|-------|--------|---------------|-------------|---------|------------------|------|----------|----------------|
| 1 | SIMPLE | dates | ALL | | | | | 95 | 100.00 | Using filesort |
| 1 | SIMPLE | oi | ref | xxxxxx | order_id_fk | 4 | const | 228 | 100.00 | |
| 1 | SIMPLE | o | eq_ref | xxxxx | PRIMARY | 4 | myDb.oi.order_id | 1 | 100.00 | Using where |
| 1 | SIMPLE | o | ref | xxxx | shop_id_fk | 4 | const | 65 | 100.00 | Using where |
| 1 | SIMPLE | oi | ref | xxx | order_id_fk | 4 | myDb.o.id | 1 | 100.00 | Using where |
I want to understand why the MySQLs treat the same query that much different, and how I can tweak MySQL 5.7 to be faster?
I'm not looking for help on rewriting the query to be faster, as that is something I am already doing on my own.
There are a number of things that may cause a query to take longer time to execute: Inefficient query – Use non-indexed columns while lookup or joining, thus MySQL takes longer time to match the condition. Table lock – The table is locked, by global lock or explicit table lock when the query is trying to access it.
MySQL 5.7 is 3x faster than MySQL 5.6, delivering 1.6 Million SQL Queries Per Second.
As shown in the graph above, MySQL 8.0 performance shows again a huge difference in the time it takes to process transactions. The lower, the better it performs which means it's faster to process transactions.
As can be read in the comments, @wchiquito has suggested to look at the optimizer_switch
. In here I found that the switch derived_merge
could be set to off, to fix this new, and in this specific case undesired, behaviour.
set session optimizer_switch='derived_merge=off';
fixes the problem.
(This can also be done with set global ...
or be put in the my.cnf / my.ini)
Building and maintaining a "Summary Table" would make this query run much faster than even 1 second.
Such a table would probably include shop_id
, date
, and some count.
More on summary tables.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With