Full Text Search

krotkruton

First off, I'm using MySQL 3.23 so I'm not sure if Full Text Searches are even in that version since there isn't any documentation for versions before 4.

My table is set up like this:

Keyname    Type      Cardinality  Field 
PRIMARY     PRIMARY   2            id  

profile     FULLTEXT  None         profile  

sig         FULLTEXT  None         sig  

last_name   FULLTEXT  None         last_name  

first_name  FULLTEXT  None         first_name

With 5 fields (all of which are listed, obviously) where id is an int that auto_increments and all 4 other fields are varchar of different sizes.

I have two entries in the table, one of them has first_name and last_name as Brendan Neff and the other is Sam Smith. When I do the following query, I get the error listed:

SELECT * FROM `fulltexttest` WHERE MATCH (`first_name`,`last_name`) AGAINST ('Neff');

#1191 - Can't find FULLTEXT index matching the column list

I feel like I'm following the model that is listed here http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html
but it just won't work... Is my version the problem or does anyone have any other advice?

justsomeone

 SELECT * FROM `fulltexttest` WHERE MATCH (`first_name`,`last_name`) AGAINST ('Neff');

What are you trying to achieve? Do you just want to select all rows where either first_name or last_name is 'Neff'?

I would suggest the simpler:

SELECT * FROM tablename WHERE first_name = 'Neff' OR last_name = 'Neff';

Or am I missing some extra benefits provided by your method?

krotkruton

Now that you said that, I realized how stupid my example was.
I want to be able to use the FULLTEXT searches the way they were meant to be used, or at least the way that I understand it, being that you can take a large block of 'words' and search for a single 'word' among that.

The point I was trying to make with that example is that something is wrong with what I am doing because I know that "Neff" is contained in one of those two fields instead of giving some large example where I post a long block of code and search for some random word among that... In fact, it would make much more sense if I had used this paragraph right here as an entry in say, 'profile' and then:

SELECT * FROM `fulltexttest` WHERE MATCH (`first_name`,`last_name`,`profile`) AGAINST ('random');

So from my understanding, that should return the row where the previous paragraph is in the profile table because 'random' is in that paragraph...

Basically, my point was that if I can't get it to work for searching 4 words in two different columns, how am I going to get it to work for larger columns of text, but then again I know next to nothing about FULLTEXT because I haven't had a chance to mess with it much yet cuz I can't get it working.

Anyway, you're absolutely right that my example was trivial because it would be ridiculous to do it the way I did it, but I should be able to do it if I wanted to for some reason.

Thanks for pointing that out though because I bet a bunch of people just looked over this problem because the example was so trivial.

justsomeone

I haven't used fulltext. To tell ya the truth I hadn't even heard of it before your post - so I mat not be best placed to help ya. If I wanted to search for a word across a couple of fields I would do.

select * from tablename where field1 like '%Ryan%' or field2 like '%Ryan%' or ...

But fulltext may be a much more efficient elegant way of doing that kind of stuff