MySQL Forums
Forum List  »  Performance

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow query
1998
April 21, 2015 11:15AM
846
April 22, 2015 07:17AM
781
April 22, 2015 07:05PM
988
April 23, 2015 11:42PM
783
April 23, 2015 11:55PM
764
April 24, 2015 05:22PM
764
April 25, 2015 02:31AM
848
April 25, 2015 02:41AM
1472
April 25, 2015 05:22AM
817
April 25, 2015 10:42PM
794
May 13, 2015 03:30AM
872
April 28, 2015 03:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.