MySQL Forums
Forum List  »  Performance

Re: Left join optimization- query is very slow
Posted by: Rick James
Date: March 06, 2015 06:48PM

You really should normalize those VARCHAR fields.

Assuming the charset fix does not suffice, here are 3 possible improvements.

INDEX(tag_name, imei, id) -- this index is mostly needed for this first query:
-- #1
SELECT  v1.`imei`, v2.`device_id`, v2.`user_gmail_id`, MAX(u.`app_version`)
    FROM  
      ( SELECT  id, imei
            FROM  view_tracker
            WHERE  tag_name IN (
                    'Gmobipush_RS_MMX_1_4_9_Test',   'Gmobipush_RS_iBall_1_4_9_Test',
                    'Gmobipush_RS_Intex_1_4_9_Test', 'Gmobipush_RS_MMX_1_4_9',
                    'Gmobipush_RS_iBall_1_4_9',      'Gmobipush_RS_Intex_1_4_9' ) 
      ) v1
    JOIN  view_tracker v2 USING(id)
    LEFT JOIN  userdeviceinfo u ON u.imei = v1.imei
    GROUP BY  v1.imei; 
-- #2
SELECT  v.`imei`, v.`device_id`, v.`user_gmail_id`, MAX(u.`app_version`)
    FROM  
      ( SELECT  id, imei, device_id, user_gmail_id
            FROM  view_tracker
            WHERE  tag_name IN (
                    'Gmobipush_RS_MMX_1_4_9_Test',   'Gmobipush_RS_iBall_1_4_9_Test',
                    'Gmobipush_RS_Intex_1_4_9_Test', 'Gmobipush_RS_MMX_1_4_9',
                    'Gmobipush_RS_iBall_1_4_9',      'Gmobipush_RS_Intex_1_4_9' ) 
      ) v
    LEFT JOIN  userdeviceinfo u ON u.imei = v1.imei; 
-- #3
SELECT  `imei`, `device_id`, `user_gmail_id`, 
        ( SELECT  MAX(`app_version`) FROM  userdeviceinfo  WHERE  imei = v.imei ) AS max_ver
    FROM  view_tracker
    WHERE  tag_name IN (
                    'Gmobipush_RS_MMX_1_4_9_Test',   'Gmobipush_RS_iBall_1_4_9_Test',
                    'Gmobipush_RS_Intex_1_4_9_Test', 'Gmobipush_RS_MMX_1_4_9',
                    'Gmobipush_RS_iBall_1_4_9',      'Gmobipush_RS_Intex_1_4_9' )
I can't predict which approach will be best; try them all.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Left join optimization- query is very slow
2255
March 06, 2015 06:48PM


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.