Trying to figure out FULLTEXT with much frustration!

[tomccabe]

Hi all,

 

I'm sort of a newbie PHP/MySQL developer learning some more complex stuff. Usually a good Google search can explain a feature, but I have had NO luck understanding FULLTEXT indexes/searching. Here's the table structure I'm trying to search from a search form:

 

CREATE TABLE `products` (

`prod_id` int(5) NOT NULL AUTO_INCREMENT,

`prod_title` varchar(128) NOT NULL,

`live` varchar(3) NOT NULL DEFAULT 'no',

`featured` varchar(3) NOT NULL DEFAULT 'no',

`price` int(7) NOT NULL,

`short_desc` varchar(400) NOT NULL,

`long_desc` varchar(612) NOT NULL,

`quantity` int(4) NOT NULL,

`picture` varchar(3) NOT NULL,

`pic_ext` varchar(4) NOT NULL,

PRIMARY KEY (`prod_id`),

FULLTEXT KEY `prod_title` (`prod_title`,`short_desc`,`long_desc`)

) ENGINE=MyISAM AUTO_INCREMENT=104 DEFAULT CHARSET=cp1257

 

------------------------

 

Now what I've been trying to do is perform searches across the 3 fields under the FULLTEXT index (which, hell, I'm not even sure is set up correctly!) with multiple search terms. For instance:

 

SELECT * FROM products WHERE MATCH(prod_title,long_desc,short_desc) AGAINST ('some words here')

 

It seems to be able to grab certain words from certain fields and certain words it refuses to find. It's maddening and I'm probably not understanding how this works. Can anyone help?

[andrewgauger]

A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match. Full-text searches are natural language searches if the IN NATURAL LANGUAGE MODE modifier is given or if no modifier is given.

 

There are a few reasons it isn't working as expected, but are the many entries or are you testing this before putting it in production?  Often it comes from searching for a word that exists in 50% of the rows when you are throwing it against a test bed.  Also try out http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html

 

The fact that some of the searches issue valid results (and the syntax of your MySQL post) seems like you have it all set up right, just make sure you understand the ins and outs of FULLTEXT and http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html is your best reference.

[tomccabe]

Thanks Andrew. At least I know I configured it correctly. I think the issue is arising because I loaded the DB with a bunch of dummy products for development (the client is going to be populating the DB at which time I'll flush the current "products"). Most of them have the same name format. Duh. Good reading though to understand this!