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