mysql 5.7.10 performance 3 Times Slower vs 5.6.28

Question

This weekend I rolled back my database from 5.7.10 to 5.6.28. In a test load of 27 million records with inserts of multi-writes, the MySQL 5.7.10 took 120 minutes to load this database. On the same machine, 5.6.28 took 40 minutes. This is a repeatable event. The test was done on a Windows 7 Professional machine having 64GB of memory. And it was done on a Server with Windows Serve 2012 R2 with 256 GB of memory.

This was not the only problem. The group by statement fails to operate on a sub query and always operates on the table inside the query. Example:

select * from (select * from tablename order by datex desc, timex desc ) as A 
group by recordx;

This query should pull the first record of the A Table as in 5.6.28. In 5.70.10, the query pulls out the earliest record entered from tablename and NOT A. Even when desc is replaced with asc, the result is the same.

5.6.28 always picks up the top element in the "A" table of the subquery.

MySQL needs some explaining to do with respect to their claim of 5.7.10 being 3x faster. It is definitely not backward compatible either as demonstrated by the "group by" behaviour.

Answer 1

This answer does not address the performance issue (I think more information is needed and that should be a separate question. It addresses the query issue.

MySQL documentation is quite explicit that this query:

select *
from (select * from tablename order by datex desc, timex desc ) as A
group by recordx

produces indeterminate results. Here is the relevant quote:

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

It is a shame that your developers used code that is explicitly documented not to work correctly. You should fix the code.

One fix is:

select * 
from tablename
where not exists (select 1
                  from tablename t2
                  where t2.recordx = t.recordx and
                        (t2.datex > t.datex or
                         t2.datex = t.datex and t2.timex > t.timex
                        )
                 );