Problem with fulltext searching/MySQL

[djresonance]

Hi,

I'm getting the following error message when trying to do a fulltext search against two or more columns in my table:

ERROR 1191: Can't find FULLTEXT index matching the column list

I have added fulltext indexes to all of the columns I'm searching against. The query I'm using is:

SELECT document_id, title, MATCH(title, question) AGAINST ('ms4 login') AS score FROM template_howto;

The odd thing seems to be that if I instead try to match against any one column, the query will work fine. However matching against one column is useless for my purposes so I need to figure out why I'm getting this error message. I'm using MySQL 3.23.49 on Windows NT 4.

Any suggestions? I've read the MySQL fulltext manual section several times and searched google but haven't found any reference to this problem.

Thanks,
Brandon

[djresonance]

After some more research, I found out the problem, and I'll document it here in case someone comes looking for it in the future.

My first problem was that I created a seperate fulltext index for each column that I wanted to search on. The solution to that was to drop all of the indexes and add one index like this:

ALTER TABLE template_howto ADD FULLTEXT(document_id, title, question, answer)

The second problem was that when you're specifying the MATCH clause, the column names have to match up exactly with the column names that you created on your index.

With the above fulltext key, I was trying to write a query like this:

SELECT document_id, MATCH(title, question) AGAINST ('outlook') FROM template_howto

That gave me this error ERROR 1191: Can't find FULLTEXT index matching the column list

When I rewrote the query to include all columns that were on the index, it worked as expected:

SELECT document_id, MATCH(document_id, title, question, answer) AGAINST ('outlook') FROM template_howto

I hope this helps someone down the road.
Brandon