Solved

How can I speed up this select join query?

Posted on 2008-11-01
14
188 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
 
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
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.

 
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

Suggested Solutions

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now