Following query is taking a long time (upto 10 secs) to return the resultset.
Would greatly appreciate if someone could help me understand why.
I have run 'analyze table <tablename>' on all the three tables involved. The
EXPLAIN output, record count and table description is also included.
SELECT SQL_CALC_FOUND_ROWS art.article_id aId, art.title, aFt.seq_no sn,
SUBSTRING(abs.abstract,1,10) abs
FROM art, abs, aFt
WHERE (
MATCH(art.title, art.subtitle, art.keywords, art.general_terms) AGAINST
('+recommender +systems' IN BOOLEAN MODE)
OR
MATCH(abs.abstract) AGAINST ('+recommender +systems' IN BOOLEAN MODE) )
AND art.article_id = aFt.article_id
AND art.article_id = abs.article_id
LIMIT 5
;
aId title sn abs
245121 Recommender systems 1
245127 Recommender systems for evaluating 1
331413 Workshop on recommender systems 1
353475 PYTHIA-II 1 Often scie
353481 Mining and visualizing recommendation 1 In this pa
table type possible_keys key key_len ref rows Extra
aFt index PRIMARY PRIMARY 4 NULL 53191 Using index
art eq_ref PRIMARY PRIMARY 3 aFt.article_id 1
abs eq_ref PRIMARY PRIMARY 3 art.article_id 1 Using where
CREATE TABLE art ( -- Records: 54668
article_id mediumint(9),
title varchar(255),
subtitle varchar(127),
keywords mediumtext,
general_terms tinytext,
PRIMARY KEY (article_id),
FULLTEXT KEY title (title,subtitle,keywords,general_terms)
) TYPE=MyISAM;
CREATE TABLE abs ( -- Records: 54681
article_id mediumint(4),
abstract mediumtext,
PRIMARY KEY (article_id),
FULLTEXT KEY abstract (abstract)
) TYPE=MyISAM;
CREATE TABLE aFt ( -- Records: 53191
article_id mediumint(9),
seq_no tinyint(4),
PRIMARY KEY (article_id,seq_no)
) TYPE=MyISAM;
I am using mysql Ver 12.21 Distrib 4.0.15, for mandrake-linux-gnu (i586).
Thanks,
Nishi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
