consulta extremamente lenta após a migração para o mysql 5.7
Eu tenho um banco de dados MySQL com tabelas InnoDB que resumem mais de 10 a 10 GB de dados que eu quero migrar do MySQL 5.5 para o MySQL 5.7. E eu tenho uma consulta que se parece um pouco com:
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;
Meu problema é que esta consulta está executando rapidamente no MySQL 5.5, mas extremamente lenta no MySQL 5.7.
No MySQL 5.5, leva mais de 1 segundo no início e <0,001 segundos a cada execução recorrente sem reiniciar o MySQL.
No MySQL 5.7, são necessários 11,5 segundos no início e 1,4 segundos em cada execução recorrente sem reiniciar o MySQL.
E quanto mais LEFT JOINs adiciono à consulta, mais lenta ela se torna no MySQL 5.7.
Agora, ambas as instâncias são executadas na mesma máquina, no mesmo disco rígido e com as mesmas configurações do my.ini. Portanto, não é hardware.
Os planos de execução diferem e eu não sei o que fazer com isso.
Este é o EXPLAIN ESTENDIDO no 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 |
Este é o EXPLAIN ESTENDIDO no 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 |
Quero entender por que os MySQLs tratam a mesma consulta de maneira muito diferente e como posso ajustar o MySQL 5.7 para ser mais rápido?
Não estou procurando ajuda para reescrever a consulta para ser mais rápida, pois isso é algo que já estou fazendo sozinho.