title: query extremely slow after migration to mysql 5.7
body: <p>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:</p>
  
  <pre><code>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 &gt;= mySub1.time_start AND dates.date &lt; 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 &gt;= mySub2.time_start AND dates.date &lt; mySub2.time_end
  GROUP BY dates.date;
  </code></pre>
  
  <p>My problem is that this query is performing fast in MySQL 5.5 but extremely slow in MySQL 5.7.</p>
  
  <p>In MySQL 5.5 it is taking over 1 second at first and &lt; 0.001 seconds every recurring execution without restarting MySQL.<br>
  In MySQL 5.7 it is taking over 11.5 seconds at first and 1.4 seconds every recurring execution without restarting MySQL.<br>
  And the more LEFT JOINs I add to the query, the slower the query becomes in MySQL 5.7.</p>
  
  <p>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.<br>
  The execution plans do differ, though and I don't know what to make from it.</p>
  
  <p>This is the EXPLAIN EXTENDED on MySQL 5.5:</p>
  
  <pre><code>| 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     | &lt;derived2&gt; | ALL   |               |             |         |           | 281   | 100.00   | ''                              |
  | 1  | PRIMARY     | &lt;derived3&gt; | 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                     |
  </code></pre>
  
  <p>This is the EXPLAIN EXTENDED on MySQL 5.7:</p>
  
  <pre><code>| 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    |
  </code></pre>
  
  <p>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?<br>
  I'm not looking for help on rewriting the query to be faster, as that is something I am already doing on my own.</p>