About FULLTEXT index

[orange08]

hi, i wish to use FULLTEXT index in my php website, for searching purpose. but, i'm not quite understand with this FULLTEXT, so can anyone please help me and tell me actually in what situation need to use this type of searching?

 

so that i can decide whether there is a need for me to use it or just use the simple search method.

[Chicken Little]

It should be faster and more efficient. For more info see

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

[Mchl]

You would use FULLTEXT index for example to perform searches in content of forum messages. In other words, when you need to search in large blocks of text.

[phparray]

FULLTEXT index only works with the MyISAM storage engine. In other words you can not use it on a table that also uses foreign keys.  So if you happen to be using the InnoDB engine you'll need to work something else out for searches.  In many cases this will do.

 

SELECT field WHERE field LIKE  "%value%";

[fenway]

Sphinx is much better.

[orange08]

FULLTEXT index only works with the MyISAM storage engine. In other words you can not use it on a table that also uses foreign keys.  So if you happen to be using the InnoDB engine you'll need to work something else out for searches.  In many cases this will do.

 

SELECT field WHERE field LIKE  "%value%";

 

this is my problem, my table need to be innodb, but i want to use FULLTEXT index...

 

yup, i have think about your code too, but how about user enter a phrase for search keyword? with the above code, only when the data is exact match the phrase only will be detected. how about if the search phrase is 'happy family', but i want record with either got 'happy' or 'family' to be detected?

[Mchl]

this is my problem, my table need to be innodb, but i want to use FULLTEXT index...

 

You can't. It's like saying you need transaction support in MyISAM.

 

You might want to take a look an Sphinx as fenway suggested. It supports both MySQL engines

[FezEvils]

for myisam engine, u have to declare the field that need to be fulltext.

then use sql like this to search "happy family". it will returned "happy family","happy", "family"

 

SELECT general.g_page,commonnames, scientificname, general.desc FROM general WHERE ( match (general.scientificname)  against('$search' in boolean mode) ) or ( match (general.commonnames)  against('$search' in boolean mode) ) or ( match (general.desc)  against('$search' in boolean mode) ) GROUP BY general.scientificname