Solved

How can I speed up this select join query?

Posted on 2008-11-01
14
192 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
[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
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 143

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the 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 143

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 143

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 143

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 143

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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 …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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