At 01:58 PM 8/2/2005, you wrote:
Just wondering if someone would be kind enough to take a look at it - Nishi
Nishi,
What did "EXPLAIN" show? Also what happens if you have just one
Match? Is it faster? If so, why not run 2 queries and build a temporary
table from the results. Using "OR" may be what's slowing down the query.
Mike
> -----Original Message-----
> 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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
