0

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

New Server

Old Server

Old Server

  • ORDER BY clause have no sense when used in subquery without a LIMIT clause. Remove them. Additionally it allows remove the most inner subquery. – Akina Oct 5 '18 at 5:02
  • Looks like this is just a count of products with or without (left join) an 'APPROVED_ATTIRIBUTES'. So why is it so complicated? Try rewriting it from scratch. The GROUP_CONCAT obviously isn't needed. Also FULL_GROUP_BY is a feature to stop you getting wrong results, recommend using it. – danblack Oct 5 '18 at 6:01
  • 1
    If you accidentally created two accounts then please have a look at the article (I accidentally created two accounts; how do I merge them?). This will allow you to directly edit your question without it being pushed into the community's review queue. Thank you. – John aka hot2use Oct 8 '18 at 7:15
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.