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

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 <konrad.lindner@gmx.net> said:

> 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

Thanks Joss,

it seems not really helping in my case...

Best,

korcs

== Quote from korcs (konrad.lindner@gmx.net)'s article
> 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

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

> 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

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