Slow query
Posted by:
Manu Swami
Date: April 21, 2015 11:15AM
Hello,
We have recently migrated an application to a newer server. In old server MySql version was 5.0. and in new server MySql version is 5.6.
I have observed a performance issue after migration. In a particular event, my application queries the DB and fetch all the respective records as per given logic. The query was getting executed in less than a minute for 1 million records. After migration, the same query takes 4-5 hours.
The query which is being executed and also logged in slow-query log is below:
SELECT dn.id DN,
dn.pt,
dnASD.asdId,
dn_imsi.imsiId,
IF (dn_imsi.imsiId IS NOT NULL,
CONCAT_WS(':',
imsi_ne.neType,
HEX(imsi_ne.neId)),
GROUP_CONCAT(CONCAT_WS(':',
dnNE.neType,
HEX(dnNE.neId)))) neList,
dnNS.nsType,
dnNS.nsdn,
dnBL.cgbl,
dnBL.cdbl
FROM dn LEFT JOIN dn_dn as dnNS ON id = dnNS.dnId LEFT JOIN dn_bl as dnBL ON id = dnBL.dnId LEFT JOIN dn_asd as dnASD ON id = dnASD.dnId LEFT JOIN dn_ne AS dnNE ON id = dnNE.dnId LEFT JOIN dn_imsi ON dn.id = dn_imsi.dnId LEFT JOIN imsi_ne ON dn_imsi.imsiId = imsi_ne.imsiId WHERE id > '\0\0\0\0hI' AND dn_imsi.imsiId IS NULL GROUP BY dn.id ORDER BY dn.id LIMIT 500;
Please note: No indexes or any object definition changed after migration.
I am a C++ developer with an average knowledge about MySql, so I may have missed something to define for DB by default such as any parameter in my.cnf file etc.
Any help is appreciated.
Kind regards,
Manu