Solved

How can I speed up this select join query?

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trigger usage 2 75
How to Generate log or file on inserting duplicate records in mysql 6 51
MySQL  on Tomcat 8 68
MySQL Sub-Select Query Returning Duplicate Result 7 45
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

829 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