-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 02, 2005 3:14 PM
To: Kapoor, Nishikant
Cc: mysql@lists.mysql.com
Subject: RE: SQL query taking a long time...please
"Kapoor, Nishikant" <[EMAIL PROTECTED]> wrote on 08/02/2005 02:58:08 PM:
> Just wondering if someone would be kind enough to take a look at it - Nishi
>
> > -----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
>
Unfortunately for you this seems to be indexed well. You can *possibly* speed
this up if you split your FT search and your other information into separate
queries but it seems from your EXPLAIN output that you are using the correct
indexes and that your coverage is rather good. Here is my idea, I do not
guarantee it will work any better than what you already have:
CREATE TEMPORARY tmpKwHits
SELECT art.article_id
FROM art
WHERE MATCH(art.title, art.subtitle, art.keywords,
art.general_terms) AGAINST ('+recommender +systems' IN BOOLEAN MODE)
UNION
SELECT abs.article_ID
FROM abs
WHERE MATCH(abs.abstract) AGAINST ('+recommender +systems' IN BOOLEAN MODE);
ALTER TABLE tmpKwHits add key(article_id);
SELECT SQL_CALC_FOUND_ROWS art.article_id aId, art.title,
aFt.seq_no sn, SUBSTRING(abs.abstract,1,10) abs
FROM tmpKwhits tkw
INNER JOIN art
on art.article_id = tkw.article_id
INNER JOIN abs
ON abs.article_id = tkw.article_id
INNER JOIN aFt
ON aft.article_id = tkw.article_id
LIMIT 5;
DROP TEMPORARY TABLE tmpKwHits;
My other idea is to change your one query into a UNION of two (to perform the
same function as your OR clause).
(SELECT 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)
AND art.article_id = aFt.article_id
AND art.article_id = abs.article_id
LIMIT 5)
UNION
(SELECT art.article_id aId, art.title,
aFt.seq_no sn, SUBSTRING(abs.abstract,1,10) abs
FROM art, abs, aFt
WHERE 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)
LIMIT 5;
My problem is I don't know into which term of the UNION you need to add the
SQL_CALC_FOUND_ROWS predicate.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Shawn,
Your solution with UNION is a huge improvement! Thank you very much. You are
good.
As for the SQL_CALC_FOUND_ROWS predicate, mySQL does not allow it to be put in
the second SELECT. It must be with the first SELECT statement, or else mySQL
complains:
-- ERROR 1234 at line 1: Wrong usage/placement of 'SQL_CALC_FOUND_ROWS'
Thanks again,
Nishi
