I have recently migrated Mysql 5.1 to Mysql 5.7. DB Migration was successful but when the application started firing few selected queries, Mysql started taking hours to execute them for which previously execution times wherein 4-5 seconds. That's why wanted to know changes between this two versions. One of the query creating issue:
select count(*) COUNT_PRODUCTS from (select d.ID, d.EAN, d.NAME, d.SAP_CATEGORY_ID, d.SAP_VENDOR_ID, d.RELEASE_DATE, d.REMOVED_DATE, d.IS_DRAFT, d.DATA_STATE, d.gc, ATTRIBUTE_CODE AS ATTRIB_NAME, ATTRIBUTE_VALUE AS APP_STATUS from (select b.ID, b.EAN, b.NAME, b.SAP_CATEGORY_ID, b.SAP_VENDOR_ID, b.RELEASE_DATE, b.REMOVED_DATE, b.IS_DRAFT, b.DATA_STATE, group_concat(pl.LIFECYCLE_VALUE) gc from (select a.ID, a.EAN, a.NAME, a.SAP_CATEGORY_ID, a.SAP_VENDOR_ID, a.RELEASE_DATE, a.REMOVED_DATE, a.IS_DRAFT, a.DATA_STATE from (select p.ID, p.EAN, p.NAME, p.SAP_CATEGORY_ID, p.SAP_VENDOR_ID, p.RELEASE_DATE, p.REMOVED_DATE, p.IS_DRAFT, p.DATA_STATE from PRODUCTS as p order by p.EAN, p.IS_DRAFT) as a group by a.EAN ) as b left join PRODUCT_LIFECYCLE_STATES pl on pl.PRODUCT_ID = b.ID group by b.ID ) as d left join PRODUCT_ATTRIBUTE_VALUES AS PRODAV ON ID=PRODAV.PRODUCT_ID AND PRODAV.ATTRIBUTE_VALUE_ID IN (select ID from ATTRIBUTE_VALUES where ATTRIBUTE_CODE='APPROVED_ATTRIBUTES') left join ATTRIBUTE_VALUES AS ATVALS ON PRODAV.ATTRIBUTE_VALUE_ID = ATVALS.ID AND ATVALS.ATTRIBUTE_CODE ='APPROVED_ATTRIBUTES' ORDER BY d.SAP_CATEGORY_ID, d.NAME ) f
I did one change in my.cnf file to suppress GROUP BY error by changing FULL_GROUP_BY. This is my first quesion , so please let me know if any further information or rectification is required.
I am not able to reply to comments. Even after removing ORDER BY clause it is taking same time of hours. Actually, this query is also used without the COUNT in some places. If I keep FULL_GROUP_BY it is ending up with an error.
After running EXPLAIN on this query I am getting the different result in the old and new server.