Solved

MySQL Query with Inner Join to small table suddenly takes FOREVER

Posted on 2014-03-06
7
522 Views
Last Modified: 2014-03-17
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
0
Comment
Question by:ducky801
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39911233
quick comment

there is absolutely no point in asking for distinct & group by...
drop use of distinct in both of your queries

select distinct (i.e. make a unique list of) X
...
group by (i.e. make a unique list of) X

i.e. you making a unique list twice
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39911246
do both tables have indexes on account & skill?
0
 
LVL 5

Author Comment

by:ducky801
ID: 39911340
Yea.  Muscle memory on the distinct.  It flows from the fingertips.....

Yes.  Both tables have and index on Account and Skill.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 5

Author Comment

by:ducky801
ID: 39911344
One other thing to note-

I'm also having this problem:
http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Windows/Q_28382342.html

I read somewhere today that long queries like the one i'm wresting with could be related to running out of space in your tmpdir.  Not sure if these could be related or how to go about testing that though.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39912018
>> "distinct.  It flows from the fingertips"
it most definitely should not flow, it should only ever be used when it is absolutely needed

I haven't more specific ideas on the actual question I'm afraid.  Are your statistics current? Do you need to analyse those tables perhaps?
see:
https://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-persistent-stats
(assuming innob)
0
 
LVL 5

Accepted Solution

by:
ducky801 earned 0 total points
ID: 39924680
I was able to get the query to run 180x faster by replacing the inner join with a straight_join.

Straight_join overrides the query optimizer when it makes a poor decision.

http://dev.mysql.com/doc/refman/5.0/en/join.html
0
 
LVL 5

Author Closing Comment

by:ducky801
ID: 39933757
I found this answer on a separate message board.  Other expert comments on this post didn't help the query to run faster
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question