-----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
 
 

Reply via email to