MySQL Forums
Forum List  »  Performance

Query with Inner Join to small table suddenly takes FOREVER
Posted by: Adam Reese
Date: March 06, 2014 05:05PM

Below are 2 queries. They're exactly the same except for the inner join to the t_lob table (which has 14 rows in it and an index on account and skill). The query with the join used to run in a matter of seconds before I upgraded to MySQL v5.6 (from 5.5). Now it crawls.

Why? What can I do to fix it?

Here are the explain results:

Query with Join:
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,<derived2>,ALL,NULL,NULL,NULL,NULL,6066,"Using temporary; Using filesort"
2,DERIVED,l,ref,"Account,Skill",Account,11,const,6,"Using index condition; Using where"
2,DERIVED,t,ref,"StartDate,Skill,Account",Skill,103,att.l.Skill,1011,"Using where"


Query with Join Commented out:
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,<derived2>,ALL,NULL,NULL,NULL,NULL,445155,"Using temporary; Using filesort"
2,DERIVED,t,range,"StartDate,Account",StartDate,4,NULL,593540,"Using index condition; Using where"



.....So, it seems to me that the inclusion of the inner join causes fewer rows to be evaluated. However, as I've explained it actually takes longer. Not even sure if it will complete. I've let it go for about 30 min before killing it. The version without the join takes 10.8 seconds

To clarify. t_transcripts has about 10M rows in it. t_lob has 14.


select distinct
#lob,
URL_Cleaned, count(URL_Cleaned) as URL_Count, sum(convcounter) as Conversions

from (select
t.account,
#l.lob,
#l.sortseq,
t.sessionid,
t.skill,
t.startdate,
t.chat_button_name,
t.chatreferer as URL,
substring_index(substring_index(substring_index(t.chatreferer, '://',-1),'#', 1), '?',1) as URL_Cleaned,
if(isnull(conversion), 0, 1) as convcounter

from
t_transcripts t

#inner join t_lob l
#on t.account = l.account
#and t.skill = l.skill

where
t.account = '12345678'
and t.startdate between '2014-02-12' and '2014-02-28'
)q

group by
#lob,
url_cleaned



-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SLOW QUERY (inner join is not commented out):




select distinct
#lob,
URL_Cleaned, count(URL_Cleaned) as URL_Count, sum(convcounter) as Conversions

from (select
t.account,
#l.lob,
#l.sortseq,
t.sessionid,
t.skill,
t.startdate,
t.chat_button_name,
t.chatreferer as URL,
substring_index(substring_index(substring_index(t.chatreferer, '://',-1),'#', 1), '?',1) as URL_Cleaned,
if(isnull(conversion), 0, 1) as convcounter

from
t_transcripts t

inner join t_lob l
on t.account = l.account
and t.skill = l.skill

where
t.account = '12345678'
and t.startdate between '2014-02-12' and '2014-02-28'
)q

group by
#lob,
url_cleaned

Options: ReplyQuote


Subject
Views
Written By
Posted
Query with Inner Join to small table suddenly takes FOREVER
2597
March 06, 2014 05:05PM


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.