I have the following table.
CREATE TABLE `keywords` (
`Word` varchar(20) NOT NULL,
`PadID` bigint(20) NOT NULL,
`LetterIdx` varchar(1) NOT NULL,
KEY `Word` (`Word`),
KEY `LetterIdx` (`LetterIdx`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
And heres some example data.
INSERT INTO `keywords` (`Word`, `PadID`, `LetterIdx`) VALUES
('error', 65010, 'E'),
('fix', 65010, 'F'),
('clean', 65010, 'C'),
('windows cleaner', 65010, 'W'),
('registry tools', 65010, 'R'),
('windows tweak', 65010, 'W'),
('windows error', 65010, 'W'),
('system maintainance', 65010, 'S'),
('registry repair', 65010, 'R'),
('registry cleaner', 65010, 'R');
PadID is the key to my main table (My main table is called Pads, records for programs I show on my website, its a shareware download site), LetterIdx is the first letter of Word.
So I can product a list of word with the same letter.
Heres an example.
http://www.softtester.com/keywords-b.shtmlI have program page on my website, which show details about one program.
I have a query to create a list of 10 programs which match the current program based on keywords.
select count(match_keywords.Word)
as matching_words ,pads.* from keywords current_program_keywords left join keywords match_keywords on match_keywords.Word=curren
t_program_
keywords.W
ord inner join pads on pads.padID=match_keywords.
PadID where match_keywords.Word IS NOT NULL and current_program_keywords.P
adID=59621
group by match_keywords.PadID order by matching_words DESC LIMIT 0,11;
The query finds the most matches of keywords and order by the most number of matches.
Unfortunately the query takes 11 seconds and my dynamic page is called a lot as its the main page on my site, I mean the same PHP is used to show different records a lot.
I need to make the query very quick, HELP!
PLEASE FEEL FREE TO ASK ANY QUESTIONS.
Start Free Trial