MySQL Forums
Forum List  »  Full-Text Search

A Fulltext search get less than a like %aa%,why?
Posted by: chen yawei
Date: February 08, 2010 08:26PM

i use a like query like

"SELECT DISTINCT a.id as key1, u.id as key2, cc.id as key3, a.title, a.sectionid, a.catid, a.created, CASE YEAR(a.modified) WHEN 0 THEN a.created ELSE a.modified END As modified, a.created_by, a.created_by_alias, u.name AS author, a.hits, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END AS slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END AS catslug , "-" AS voting FROM jos_content AS a LEFT JOIN jos_jf_content AS jf ON jf.reference_id = a.id LEFT JOIN jos_users AS u ON u.id = a.created_by LEFT JOIN jos_categories AS cc ON a.catid = cc.id WHERE 1 AND a.sectionid = 2 AND a.access <= 0 AND (a.state = 1) AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2010-02-09 01:35:52' ) AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2010-02-09 01:35:52' ) AND LOWER( a.title ) LIKE '%waste%' OR ( ( LOWER( a.introtext ) LIKE '%waste%' ) OR ( LOWER( a.fulltext ) LIKE '%waste%' ) OR ( LOWER( a.metakey ) LIKE '%waste%')) "
and i got 124 records,take about 12.91sec.

if i use a fulltext search like
"SELECT DISTINCT a.id as key1, u.id as key2, cc.id as key3, a.title, a.sectionid, a.catid, a.created, CASE YEAR(a.modified) WHEN 0 THEN a.created ELSE a.modified END As modified, a.created_by, a.created_by_alias, u.name AS author, a.hits, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END AS slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END AS catslug , "-" AS voting FROM jos_content AS a LEFT JOIN jos_jf_content AS jf ON jf.reference_id = a.id LEFT JOIN jos_users AS u ON u.id = a.created_by LEFT JOIN jos_categories AS cc ON a.catid = cc.id WHERE 1 AND a.sectionid = 2 AND a.access <= 0 AND (a.state = 1) AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2010-02-09 01:33:30' ) AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2010-02-09 01:33:30' ) AND MATCH (a.title,a.introtext,a.fulltext,a.metakey) AGAINST(' "waste" ' IN BOOLEAN MODE) "
i got only 74 records take about 7.41sec

who can tell me why?

Options: ReplyQuote


Subject
Views
Written By
Posted
A Fulltext search get less than a like %aa%,why?
5647
February 08, 2010 08:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.