I want some performance improvement guidance, my query takes several seconds to operate which is leading to problems around the server. This question works on the most typical page on my small site. I believe a radical re-think might be needed.

~ EDIT ~ This question produces a listing of records whose key phrases match individuals from the program (record) being queried. My website is really a software download directory. Which list can be used around the program listing page to exhibit other similar programs. PadID may be the primary key from the program records during my database.

~ EDIT ~

Heres my query

 select match_keywords.PadID, count(match_keywords.Word) as matching_words 
 from keywords current_program_keywords 
 inner join keywords match_keywords on
 where match_keywords.Word IS NOT NULL 
 and current_program_keywords.PadID=44243 
 group by match_keywords.PadID 
 order by matching_words DESC 
 LIMIT 0,11;

Heres the query described. alt text

Heres some sample data, however doubt you'd have the ability to begin to see the results of any performance tweaks without more data, that we can offer if you want.

   `Word` varchar(20) NOT NULL,
   `PadID` bigint(20) NOT NULL,
   `LetterIdx` varchar(1) NOT NULL,
   KEY `Word` (`Word`),
   KEY `LetterIdx` (`LetterIdx`),
   KEY `PadID_2` (`PadID`,`Word`)

 INSERT INTO `keywords` (`Word`, `PadID`, `LetterIdx`) VALUES
 ('tv', 44243, 'T'),
 ('satellite tv', 44243, 'S'),
 ('satellite tv to pc', 44243, 'S'),
 ('satellite', 44243, 'S'),
 ('your', 44243, 'X'),
 ('computer', 44243, 'C'),
 ('pc', 44243, 'P'),
 ('soccer on your pc', 44243, 'S'),
 ('sports on your pc', 44243, 'S'),
 ('television', 44243, 'T');

I have attempted adding a catalog, but this does not make much difference.

 ALTER TABLE `keywords` ADD INDEX ( `PadID` ) 

Do this approach, unsure if it can help but a minimum of differs:

select PadID, count(Word) as matching_words
from keywords k
where Word in (
  select Word 
  from keywords
  where PadID=44243 )
group by PadID 
order by matching_words DESC 
LIMIT 0,11

Anyway the task you need to have completed is heavy, and filled with string comparison, maybe conveying key phrases and storing only number ids within the keyword table can help to eliminate the occasions.