FULLTEXT searching - search engine

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

FULLTEXT searching - search engine

Post by JPlush76 »

I've read an article on devshed about using the FULLTEXT search for searching large amounts of rows as opposed to using the "LIKE" method

most of the information I've found on fulltext clearly says that its a better method for doing search engine type searches....

couple questions/points....

I have a test database with 24 records/rows

I searched for the word "camera" which is present in 3 rows and it displayed perfectly

I searched for the world "film" which is present in 17 of the 24 rows and it returned 0 results

I imagine this is the 50% rule

how do you get around that? do you send a message back saying be more specific in your search?

do you do an if statement that if mysql_num_rows = 0 do the same search using the LIKE method?


and does using fulltext increase the table size? thanks all!
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

I imagine this is the 50% rule
how do you get around that? do you send a message back saying be more specific in your search?
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fulltext_Search
If you have a MySQL source distribution [...] you can exert more control over full-text searching behaviour. [...]
Do not alter the MySQL sources unless you know what you are doing![...]
The stopword list is defined in `myisam/ft_static.c' Modify it to your taste, recompile MySQL, and rebuild your FULLTEXT indexes.
As of Version 4.0.1, MySQL can also perform boolean full-text searches using the IN BOOLEAN MODE modifier.[...]
(note: the 50% threshold is not used)[...]
and does using fulltext increase the table size?
Yes it does. take a look at the index file's size (database/<tablename>.MYI) of the table you altered.
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post by JPlush76 »

a ha :o

Code: Select all

Then recompile MySQL. There is no need to rebuild the indexes in this case. Note: by doing this you severely decrease MySQL's ability to provide adequate relevance values for the MATCH() function. If you really need to search for such common words, it would be better to search using IN BOOLEAN MODE instead, which does not observe the 50% threshold.
so if I use boolean it doesn't observe that nasty 50% rule

thanks Volka, I check the manual earlier but I missed that part :(
Post Reply