MySQL - Performance Issue in upgrading 5.5.27 to 5.7

Question

I am working on a Query which retrieves the list of service reports along with its details. The query also returns the service report which is missing in the database by putting '--' in the details column. After spending some time on it i have came up with a query like this :-

select   
     22000+n as sSrn ,IFNULL(m.mType,'---')  machineType,  ifnull(c.custName,'---') as customerName,     IFNULL(sDos,'---')  DateOfService , IFNULL(sSrgd,'---') AS ServiceRptDate ,IFNULL(sTechnician,'---') AS  technician ,IFNULL( CAST(sPcdescription AS char(100))  ,'---') AS  remarks , IFNULL( CAST(m.machineID AS char(100))  ,'---') AS  machineID
from
    (
     SELECT  @curRow := @curRow + 1 AS n
     FROM     service CROSS JOIN  dummytable
     JOIN    (SELECT @curRow := 0) r
    ) numbergen
LEFT JOIN service  s ON sSrn = 22000+n
LEFT JOIN machine m ON s.machineID = m.machineID    
LEFT JOIN customer c ON c.custID = m.custID
LIMIT 0,10

The query actually generates a table with a lot of rows and compare it with service table data. if the service number is not consecutive, it will generate the missing report number with '--' as other columns.And the ideal result is accomplished, which is like this.

But the problem is that the query is executing very slowly when I upgrade the MySQL version to 5.7 when comparing to 5.5.27 ( 5.5.27 also gives an average performance but still usable.)) For eg:

seconds elapsed for 5.5.27 MySQL : 1.48 SEC ** **seconds elapsed for 5.7 MySQL : 14.960 SEC

Please advise on how to improve query performance in MySQL 5.7 or for the SQL.

NOTE: I also understand that the automatic sorting based on the first column is not working on 5.7 which causes me to put an order by in the query, which results in more delay.

UPDATE : EXPLAIN for 5.5.27 version

EXPLAIN for 5.7 version

Answer 1

10x performance difference smells like cached vs non-cached. Run each timing test twice; ignore the first. (This assumes you do not have the Query cache turned on.) If that is not it, please provide EXPLAIN SELECT ... for both versions.

Since you are probably using the "sequences" frequently, why not build a permanent table with numbers 1...(some large value). Then you could say FROM numbers ... WHERE n BETWEEN 1 AND 10 (and leave off the LIMIT).

If you were using MariaDB, that 'table' can be dynamically generated via the pseudo-table seq_1_to_10. Or, perhaps better: seq_22001_to_22010 and avoid the 22000+.