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 match_keywords.Word=current_program_keywords.Word 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.
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.
CREATE TABLE IF NOT EXISTS `keywords` ( `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`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 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.