Solved

How can I speed up this select join query?

Posted on 2008-11-01
14
187 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

19 Experts available now in Live!

Get 1:1 Help Now