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?