1

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.

enter image description here

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 ** enter image description here **seconds elapsed for 5.7 MySQL : 14.960 SEC enter image description here

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 enter image description here

EXPLAIN for 5.7 version enter image description here

Aswin Arshad
  • 90
  • 1
  • 11
  • a)Please add the explain output (write `explain ` infront of your select, and post the result for both databases) b) What you mean by the note. There is no `order by` in your query. An additional `order by` can, without an appropriate index, slow your query down. Without order by: take the first 10 rows (which can be the ones you want. Or not. So 5.5 might be fast, but only "correct" by chance). With order by: order *everything* first, then use the first 10 rows. c) Add your indexes or table descriptions, and some sample data. d) I'm not sure I understand the reason for the line numbering. – Solarflare Sep 15 '17 at 10:27
  • What's `dummytable`? How many rows in it? Do you really need to `JOIN` to it? – Rick James Sep 15 '17 at 16:02
  • It does not make sense to have `LIMIT` without `ORDER BY`. – Rick James Sep 15 '17 at 16:03
  • Can you provide `EXPLAIN SELECT ...` from both versions? – Rick James Sep 15 '17 at 16:06
  • updated with Explain. – Aswin Arshad Sep 16 '17 at 11:51
  • If i didnt add the crossjoin , then the fetching takes more time. – Aswin Arshad Sep 16 '17 at 12:04
  • More details on how i get to this far is here : https://stackoverflow.com/questions/45830194/find-missing-row-based-on-column-data-mysql – Aswin Arshad Sep 16 '17 at 12:05

1 Answers1

0

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+.

Rick James
  • 135,179
  • 13
  • 127
  • 222