Page 1 of 1
What does 'fulltext' do?
Posted: Thu Nov 07, 2002 7:21 pm
by SL-Cowsrule
I was just wondering what setting a row to fulltext did in mySQL.
Thanks in advance,
CoW
Posted: Fri Nov 08, 2002 1:52 am
by twigletmac
Here's what the manual has to say about it:
http://www.mysql.com/doc/en/Fulltext_Search.html
Mac
Posted: Sat Nov 09, 2002 8:02 pm
by SL-Cowsrule
ugh, well i read that before posting this thread... but it makes no sense to me. Well thanks for ur help,
CoW
Posted: Mon Nov 11, 2002 2:11 am
by twigletmac
Did you get anything from the docs (you didn't mention you'd read them already so of course that's the first thing you get pointed to

), it'll be easiest to help you if you say what you think you understand and then where it totally lost you.
Mac
Posted: Tue Nov 12, 2002 9:00 am
by SL-Cowsrule
ummm well, i dont understand it at all. It sounds like its used to otimize search times and such, but i dont know how or why. I would like to know when to use it. I hope this helps,
CoW
Posted: Tue Nov 12, 2002 5:01 pm
by Rob the R
It's used with the MATCH function to be able to search for words (case-insensitive) in all of the columns specified in the FULLTEXT index. The MATCH function will return a "relevance" value that is an attempt to quantify how relevant the target word is to that row in the table. I'm sure you've seen this type of behavior in some internet search engines.
Using the docs' example:
Code: Select all
SELECT * FROM articles
WHERE MATCH (title,body) AGAINST ('database');
will return all rows from the ARTICLES table whose TITLE or BODY columns contain the
word "database", assuming a FULLTEXT index is defined on the (TITLE,BODY) columns.
If you do plan on using MATCH, though, it will definitely help you to understand the docs since the function has some complex behaviours (like the 50% threshhold) that could be quite unexpected. Basically it comes down to the fact that it is designed for searches on a large number of rows, and so using it on small tables might not return useful results.
Posted: Wed Nov 13, 2002 12:52 pm
by JPlush76
I'm using it for my company's website now (the fulltext searching) and its got some issues that make me a little unhappy....
Mainly it doesn't search on words 3 letters or less, which makes it difficult to find things in searches even if that 3 letter word is unique.
Also, since it deals with relevance if I search on "KODAK FILM"
I get at the top everything with Kodak and Film , then I get everything with either kodak or film in the description. Now you're talking about tons of pages being returned.
I think I'm just going to rewrite it after we go live to handle these issues.