Hello all!
I am glad i found this site and i have to say it is awesome! Helped me in a lot of ways using mysql.
Here is my problem. I have two tables in which i store and index keywords for search engine purpose.
Tables are:
- keyword2 (230.000 rows)
CREATE TABLE keyword2
( id
int(10) unsigned NOT NULL auto_increment, keyword
varchar(50) NOT NULL default ‘’, PRIMARY KEY (id
), UNIQUE KEY keyword
(keyword
)) ENGINE=MyISAM;
- keyword2_index (almost 5.000.000 rows)
CREATE TABLE keyword2_index
( item_id
int(10) unsigned NOT NULL default ‘0’, keyword_id
int(10) unsigned NOT NULL default ‘0’, cat
enum(‘a’,‘s’,‘l’) NOT NULL default ‘a’, type
enum(‘a’,‘t’) NOT NULL default ‘a’, PRIMARY KEY (item_id
,keyword_id
,cat
), KEY keyword_id
(keyword_id
)) ENGINE=MyISAM;
in table keyword2 are unque keywords for songs, albums, lyrics
in table keyword2_index are keyword id, song/lyric/album id, category and type of keyword (belonging to artist or title).
now to the query…
for instance i want to search for lyric with following keywords:
Shakira feat. Wyclef Jean Hips Don’t Lie
I strip string with php so i get clean keywords:
shakira feat wyclef jean hips don t lie
query:
SELECT keyword2_index.item_id AS id, COUNT(keyword2_index.item_id) AS numFROM keyword2_index,keyword2WHERE keyword2.id=keyword2_index.keyword_id AND keyword2_index.cat=‘l’ AND ( keyword2.keyword=‘shakira’ OR keyword2.keyword=‘feat’ OR keyword2.keyword=‘wyclef’ OR keyword2.keyword=‘jean’ OR keyword2.keyword=‘hips’ OR keyword2.keyword=‘don’ OR keyword2.keyword=‘t’ OR keyword2.keyword=‘lie’)GROUP BY keyword2_index.item_id ORDER BY num DESC;
this takes up to 50 seconds to execute. If i do it without COUNT … GROUP BY it takes less then a second.
I need the results to be grouped like that so i get the relevance of the search… from items with most matching keywords to items with less keywords (usualy i don’t display items with only one matching keyword)
EXPLAIN query…
id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE keyword2 range PRIMARY,keyword keyword 150 NULL 8 Using where; Using temporary; Using filesort1 SIMPLE keyword2_index ref keyword_id keyword_id 4 keyword2.id 21 Using where
i am using server with 1.5 Ghz and 512 RAM with Mysql 4.1.14
so… is there any other solution for this?
I hope you can help me.
Thank you in advance!