FULLTEXT Search troubles

[nathj]

Hi,

I am just trying to develop a search system for my website. The PHP side of things is no trouble at all. I have also set up what I think are appropriate FULLTEXT indices on the tables I will need to search.

I was just trying things out and have got a bit confused. What I think should be happening, based on what I've read, is not happening.

I have a table - tbl_resource, on this table there is FULLTEXT index on three fields - display, title and synopsis.

Display and title are varchar fields and synopsis is a mediumtext field

The table contains 7 rows at present (there will be lots more as the site goes live), three of these rows contain the phrase 'Hope08', of these three only one of them contains the word 'first'.

select 
ID, display, title, synopsis,
MATCH(display,title,synopsis) AGAINST('Hope08 first') as Relevance
FROM tbl_resource 
where MATCH(display,title,synopsis) AGAINST ('+Hope08 +first' IN BOOLEAN MODE) ORDER BY Relevance DESC

That's my SQL code and I would expect this to give me one row in the results.

However I get three rows where they all contain Hope08. I have even tried using -first in the query but that still gives me three rows and not the expected 2.

I have tried using the + operator in the SELECT part of the statement and even adding IN BOOLEAN MODE there. the results don't vary.

Clearly I am doing something wrong but I have been unable to figure out what on my own. I'd really appreciate some help with this.

Many thanks
nathj

[Atli]

The word "first" is a stopword, so it would be excluded from a boolean search.

[nathj]

The word "first" is a stopword, so it would be excluded from a boolean search.

Atli,

That would explain it. this leads to a new question.

There are rather a lot of stopwords, how do you use them in a search and actually search for them?

Cheers
nathj

[nathj]

Actually scratch that. I've done some more reading and I now understand what stopwords are. I guess there are ways around this but they are fairly complex and the end results is not really worth the hassle.

Thanks for your help