FULLTEXT issue

[chonk]

I've been racking my brain all day on this. I've done some searching and have tried a few different things from the forums that don't work. Here's my issue.

 

I create a table:

 

CREATE TABLE `Builders` (

`ID` INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

`Name` TEXT,

`BizName` TEXT,

FULLTEXT (Name,BizName)

) TYPE = MYISAM ;

 

Populate it with some data:

 

INSERT INTO Builders (Name,BizName) VALUES ('Jeffrey Yonker','Chonk Digital Design');

 

When I query using

SELECT * FROM Builders WHERE MATCH (Name,BizName) AGAINST ('Jeffrey');

I get the first table row returned.

 

However when I query using

SELECT * FROM Builders WHERE MATCH (Name,BizName) AGAINST ('Joe');

I STILL get the first table row returned;

 

Now if I add another column to the table:

INSERT INTO Builders (Name,BizName) VALUES ('Joeseph Bowers','Bowers Construction');

so that there are now 2 entries in the table and I query using

SELECT * FROM Builders WHERE MATCH (Name,BizName) AGAINST ('Jeffrey');

I get an empty set.

 

Any ideas anyone? Thanks in advance.

 

Chonk

[recklessgeneral]

Hi Chonk,

 

This looks like it is a limitation of the FULLTEXT search engine - it is designed for searching and ranking results where the fields contain large chunks of text rather than the 2 or 3 words you're using. In fact, the documentation says that in these situations you may get bizarre results.

 

For a fuller description, see http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

 

What may be more useful in this situation is to get rid of the Full Text stuff, change the Name and BizName fields to VARCHARs and use the LIKE comparator in your queries instead. Something like:

 

SELECT * FROM Builders WHERE Name LIKE '%Jeffrey%'.

 

This will return only those rows that contain the characters 'Jeffrey' anywhere in the Name field. If you want the same functionality as the FULLTEXT search, ie search across both Name and BizName, you'd have to add another OR clause to the query.

 

It looks like you can also use regular expression searches, but I have never done anything with them. Take a look here:

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

 

Hope this points you in the right direction.

Cheers,

Darren.