Professional Web Applications Themes

FULLTEXT Search Problem - MySQL

Hi All, i have a little problem with my FULLTEXT search. My TABLE structure is: CREATE TABLE `static_pages` ( `id` int(11) NOT NULL auto_increment, `title` varchar(255) default NULL, `parentId` int(11) default NULL, `priority` int(11) NOT NULL default '0', `isStatic` tinyint(1) NOT NULL default '0', `path` varchar(255) NOT NULL default '', `category_id` int(11) default NULL, `sector_id` int(11) default NULL, `content` text NOT NULL, `isActive` tinyint(4) NOT NULL default '0', `rubrik` text, `meta_title` varchar(255) default NULL, `meta_description` text, `meta_keywords` text, `notice` text, `contact_person` varchar(255) default NULL, `created` datetime default NULL, `modified` datetime default NULL, `addedBy` int(11) NOT NULL default '0', `editedBy` int(11) ...

  1. #1

    Default FULLTEXT Search Problem

    Hi All,

    i have a little problem with my FULLTEXT search.

    My TABLE structure is:

    CREATE TABLE `static_pages` (
    `id` int(11) NOT NULL auto_increment,
    `title` varchar(255) default NULL,
    `parentId` int(11) default NULL,
    `priority` int(11) NOT NULL default '0',
    `isStatic` tinyint(1) NOT NULL default '0',
    `path` varchar(255) NOT NULL default '',
    `category_id` int(11) default NULL,
    `sector_id` int(11) default NULL,
    `content` text NOT NULL,
    `isActive` tinyint(4) NOT NULL default '0',
    `rubrik` text,
    `meta_title` varchar(255) default NULL,
    `meta_description` text,
    `meta_keywords` text,
    `notice` text,
    `contact_person` varchar(255) default NULL,
    `created` datetime default NULL,
    `modified` datetime default NULL,
    `addedBy` int(11) NOT NULL default '0',
    `editedBy` int(11) NOT NULL default '0',
    `lastChange` timestamp NULL default NULL,
    PRIMARY KEY (`id`),
    FULLTEXT KEY `title` (`title`),
    FULLTEXT KEY `content` (`content`)
    ) TYPE=MyISAM AUTO_INCREMENT=190 ;

    So I have 2 Fields indexed for FULLTEXT Search.

    I have experienced that my search function does not find all the
    entries, that actually match the search.
    (I know that some rtoo common and short words are ignored when
    indexing, but I think the word 'seminar' is f.e. not that common).

    The cardinality of the 2 FULLTEXT indexes is 1, which makes me
    worried, but I am not sure, wether this is a problem or not.


    The query that I use is:

    SELECT
    id AS id, title AS title, '' AS extra, content AS description,
    MATCH (title) AGAINST ('seminar' IN BOOLEAN MODE)
    + MATCH (content) AGAINST ('seminar' IN BOOLEAN MODE) AS rating,
    'static_pages' AS table_name
    FROM static_pages
    WHERE (sector_id IN ('1', '2') AND category_id IN ('45', '36') )
    AND (isActive = 1)
    AND (MATCH (title) AGAINST ('seminar' IN BOOLEAN MODE) OR (MATCH
    (content) AGAINST ('seminar' IN BOOLEAN MODE))


    If I search with %LIKE% in the field 'content', I get 9 results, but
    with the FULLTEXT search only 1.
    (The sector_id, category_id and isActive fields are not the reason for
    this phenomen - I tried...)


    Hopefully somebody has a clue.

    Best,

    korcs

    korcs Guest

  2. #2

    Default Re: FULLTEXT Search Problem

    I am using the same MATCH as you , one on each column.. but my index
    is setup with

    ALTER TABLE proposals ADD FULLTEXT proposal_fulltext(title,
    description) (I did it after table creation)

    MATCH (proposal.title) AGAINST ('seminar' IN BOOLEAN MODE) OR MATCH
    (proposal. description) AGAINST ('seminar' IN BOOLEAN MODE)

    dont know if it can help

    joss

    On 2007-06-28 11:52:52 +0200, korcs <net> said:
     


    Josselin Guest

  3. #3

    Default Re: FULLTEXT Search Problem

    Thanks Joss,

    it seems not really helping in my case...

    Best,

    korcs

    korcs Guest

  4. #4

    Default Re: FULLTEXT Search Problem

    == Quote from korcs (net)'s article 

    i don't think you need the match/against statement in the body of the select
    statement. it is enough to have them in the predicate only. you can refer to the
    column name in the body of select:

    select
    id as id,
    content as description,
    title as rating
    from
    static_pages
    where
    blah blah
    and
    match (title, content) against ('+seminary' in boolean mode)

    --
    POST BY: lark with PHP News Reader
    lark Guest

  5. #5

    Default Re: FULLTEXT Search Problem

    > select 

    Thanks lark!

    Ok, but I need the result of the MATCH AGAINST, because I make an
    ORDER BY 'rating' at the end.

    Anyway, unfortunatelly it does not make any difference, whether I
    write it in the body or not, the result is the same...

    korcs Guest

Similar Threads

  1. FULLTEXT Search
    By starman7@hotmail.com in forum MySQL
    Replies: 3
    Last Post: December 30th, 01:00 AM
  2. Replies: 2
    Last Post: October 4th, 12:17 PM
  3. partial word search with FULLTEXT
    By creadcdd in forum MySQL
    Replies: 0
    Last Post: August 31st, 07:02 PM
  4. Tune performance of fulltext search
    By jens.bertheau@gmx.de in forum MySQL
    Replies: 3
    Last Post: February 13th, 08:59 AM
  5. MySQL UNION on 2 Fulltext search queries
    By André Nęss in forum PHP Development
    Replies: 0
    Last Post: July 21st, 11:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139