MySQL Forums
Forum List  »  Full-Text Search

Re: A Fulltext search get less than a like %aa%,why?
Posted by: Rick James
Date: February 10, 2010 12:19AM

Some of the challenges we get! Have I correctly found the difference between the two queries?...
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' )
# Either these:
      AND  LOWER( a.title ) LIKE '%waste%'
       OR  ( ( LOWER( a.introtext ) LIKE '%waste%' )
       OR  ( LOWER( a.fulltext ) LIKE '%waste%' )
       OR  ( LOWER( a.metakey ) LIKE '%waste%'))
# Or this:
      AND  MATCH (a.title,a.introtext,a.fulltext,
       a.metakey) AGAINST(' "waste" ' IN BOOLEAN MODE)

* OR, LOWER, and leading '%' all bypass any use of index --> slow
* FULLTEXT, when present, will trump all other indexes.
* 'foowaste' will match '%waste%' but will not match in fulltext. This probably explains the mismatch in counts.

If you want further discussion of speed, please provide SHOW CREATE TABLE and EXPLAIN.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: A Fulltext search get less than a like %aa%,why?
2625
February 10, 2010 12:19AM


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.