Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

query extremely slow after migration to mysql 5.7

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.

like image 469
nl-x Avatar asked Jun 09 '16 18:06

nl-x


People also ask

Why is my query taking so long MySQL?

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.

Which is faster 5.6 or 5.7 MySQL?

MySQL 5.7 is 3x faster than MySQL 5.6, delivering 1.6 Million SQL Queries Per Second.

Is MySQL 8 faster?

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.


2 Answers

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)

like image 66
nl-x Avatar answered Oct 22 '22 20:10

nl-x


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.

like image 37
Rick James Avatar answered Oct 22 '22 22:10

Rick James