• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

How can I speed up this select join query?

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
mindwarpltd
Asked:
mindwarpltd
1 Solution
 
Blackninja2007Commented:
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
 
mindwarpltdAuthor Commented:
Yes I think so.
explain.jpg
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Roger BaklundCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
mindwarpltdAuthor Commented:
I've just tried that and there didn't seem to be a noticeable time improvement.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you do have the 2 indexes:
first index: PadID + Word
and another: Word
?

0
 
mindwarpltdAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, please try to add the field Word to the PadID index.
0
 
mindwarpltdAuthor Commented:
I'm getting a "#1062 - Duplicate entry '76494-video' for key 1 "

Theres a setting to force it?
0
 
mindwarpltdAuthor Commented:
Nevermind, used a normal index not a unique one.

Should I still use your improved query, the times seems the same.
0
 
mindwarpltdAuthor Commented:
I mean their both improved.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the main thing is that the query time improved by the index change.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now