Changes between Mysql version 5.1 TO 5.7

Question

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.

New Server

Old Server

Answer 1

Do a JOIN instead of AND PRODAV.ATTRIBUTE_VALUE_ID IN (select ID ...

FULL_GROUP_BY needs to be heeded.

p:   INDEX(EAN, IS_DRAFT)  -- in this order
pl:  INDEX(PRODUCT_ID)
PRODAV:  INDEX(PRODUCT_ID, ATTRIBUTE_VALUE_ID)  -- in this order (after doing the JOIN)
ATVALS:  INDEX(ID, ATTRIBUTE_CODE)  -- in either order

PRODAV.ATTRIBUTE_VALUE_ID = ATVALS.ID -- If this is a key-value store, do not normalize the key or the value; it will make for terrible performance.

As for why the performance changed between revisions, I can only guess that things like FULL_GROUP_BY messed up the optimization. Some of my suggestions may uncork it.