Full Text Indexing / Match() Against()

[nbuzdor@project]

I thought I had SQL down-pat, but I was wrong.

I have a table with this data:
num..............name
Smallint..........varchar(50)
Primary Key.....full text indexed
--------------.....------------------
1..................Nate the Great
2..................Min Power

and I issue the query:

SELECT num, name FROM rsvp WHERE MATCH (name) AGAINST ('Power');

And I get ZERO result rows. Why!? All the examples I see at mysql.com do this and I did full-text-index the column. So anyone who can point me in the right direction would be greatly appreciated!

[pureconcepts]

For Full-text, I believe that unless you have a search term of less than 50% occurance it will not function. So with only two records, all your terms fail this condition and you receive no results in your query. Although, I am sure there are ways MySQL could have avoided this, if you think about it a 100% match or divide by zero error would occur.

Long story short. Add more records without the term 'power', and I bet your query will work. Really, the overhead for Full-text is only worth it in large tables. I imagine you are testing currently, but unless you will have a large table in the future, you may want to weight against simply using LIKE.

Verify with the documentation at www.mysql.com

[nbuzdor@project]

For Full-text, I believe that unless you have a search term of less than 50% occurance it will not function.
I figured that out during an insomnia-driven early-morning programming session a few days ago, and just forgot to resolve this thread. That was exactly the problem.
Long story short. Add more records without the term 'power', and I bet your query will work. Really, the overhead for Full-text is only worth it in large tables. I imagine you are testing currently, but unless you will have a large table in the future, you may want to weight against simply using LIKE.
I wanted to use the full-text capabilities, though LIKE would probably do the job if I simply used a regular index. Though the table will likely only have something like 400 to 1000 records in this version, I am planning on offering the technology for sale after I prove it in my situation, and don't want the query by name to time out the PHP page on a much larger instance.

[pureconcepts]

Glad you got it, I remember that stumped me the first time too.

As far as efficiency full-text is definitely better in larger tables. However, it is prone to the problem above in smaller tables. Therefore, you will need to decide which method is best.