Solved

How can I speed up this select join query?

Posted on 2008-11-01
14
189 Views
Last Modified: 2012-05-05
This is my query..

select match_keywords.PadID, count(match_keywords.Word) as matching_words from keywords current_program_keywords inner join keywords match_keywords on match_keywords.Word=current_program_keywords.Word where match_keywords.Word IS NOT NULL and current_program_keywords.PadID=32138 group by match_keywords.PadID order by matching_words DESC LIMIT 0,11


The explain on that is...

id       select_type       table       type       possible_keys       key       key_len       ref       rows       Extra
1       SIMPLE       current_program_keywords       ref       Word,PadID       PadID       8       const       19       Using temporary; Using filesort
1       SIMPLE       match_keywords       ref       Word       Word       22       jules.current_program_keywords.Word       7       Using where

The query can take 27 seconds :(
0
Comment
Question by:mindwarpltd
14 Comments
 
LVL 3

Expert Comment

by:Blackninja2007
ID: 22856192
have you checked that you have the approriate indexes created on the table ?

I dont know about mySQL but MS SQL has an execution plan to see where thing might be being held up.

0
 

Author Comment

by:mindwarpltd
ID: 22857528
Yes I think so.
explain.jpg
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22858194
can you clarify the indexes on the first table, is either of them having the padid field as first one?
for the explain plan it does not look like so?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22858289
angelIII, there is only one table... this is a self join. :)

How many rows are in the table? I did this query on a table with a few thousand rows, and it is rather heavy... it works on the cartesian product.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22858403
indeed...

now, what about this:
select match_keywords.PadID, count(match_keywords.Word) as matching_words
from keywords current_program_keywords 
inner join keywords match_keywords 
   on match_keywords.Word=current_program_keywords.Word 
where current_program_keywords.PadID=32138 
group by match_keywords.PadID 
order by matching_words DESC LIMIT 0,11

Open in new window

0
 

Author Comment

by:mindwarpltd
ID: 22865959
I've just tried that and there didn't seem to be a noticeable time improvement.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22866029
you do have the 2 indexes:
first index: PadID + Word
and another: Word
?

0
 

Author Comment

by:mindwarpltd
ID: 22866060
On the keywords table I have the following indexes

Word         INDEX         88234          Edit          Drop          Word
LetterIdx       INDEX       24        Edit       Drop       LetterIdx
PadID       INDEX       68627        Edit       Drop       PadID

So I only have indexes on one field, not a combi
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22866096
so, please try to add the field Word to the PadID index.
0
 

Author Comment

by:mindwarpltd
ID: 22866723
I'm getting a "#1062 - Duplicate entry '76494-video' for key 1 "

Theres a setting to force it?
0
 

Author Comment

by:mindwarpltd
ID: 22866802
Nevermind, used a normal index not a unique one.

Should I still use your improved query, the times seems the same.
0
 

Author Comment

by:mindwarpltd
ID: 22866805
I mean their both improved.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22866833
the main thing is that the query time improved by the index change.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

831 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