FULLTEXT For Search Troubles

Question

I've created a search before, and it works fine, but trying to do it in a different table I'm running into some issues.

I'm getting the error...

SQL query failed. Check your query.

Error Returned: Can't find FULLTEXT index matching the column list

I have (to the best of my knowledge) successfully converted the columns into fulltext and the database is set to...

Here's my SQL information...

CREATE TABLE `users` (
  `auto` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(6) DEFAULT NULL,
  `username` varchar(15) DEFAULT NULL,
  `first_name` varchar(20) NOT NULL DEFAULT '',
  `last_name` varchar(20) NOT NULL DEFAULT '',
  `email` varchar(30) NOT NULL DEFAULT '',
  `password` varchar(128) NOT NULL DEFAULT '',
  `ranking` varchar(128) DEFAULT '1',
  `sex` varchar(128) NOT NULL DEFAULT '',
  `active` varchar(128) NOT NULL DEFAULT '0',
  `ppic` varchar(128) NOT NULL DEFAULT 'ppic.jpg',
  `time_zone` varchar(128) DEFAULT 'America/Los_Angeles',
  `adult_filter` varchar(128) DEFAULT '0',
  PRIMARY KEY (`auto`),
  FULLTEXT KEY `user_id` (`user_id`,`username`,`first_name`,`last_name`,`email`,`password`,`sex`,`active`,`ppic`,`time_zone`,`adult_filter`),
  FULLTEXT KEY `user_id_2` (`user_id`,`username`,`first_name`,`last_name`),
  FULLTEXT KEY `ft_index_name2` (`user_id`,`username`,`first_name`,`last_name`),
  FULLTEXT KEY `ft_index_name32` (`user_id`,`username`,`first_name`,`last_name`,`email`,`password`,`ranking`,`sex`,`active`,`ppic`,`time_zone`,`adult_filter`),
  FULLTEXT KEY `ft_index_name322` (`user_id`),
  FULLTEXT KEY `ft_index_name3223` (`first_name`),
  FULLTEXT KEY `ft_index_name32233` (`last_name`),
  FULLTEXT KEY `ft_index_name322433` (`username`),
  FULLTEXT KEY `ft_index_name3232433` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=40 DEFAULT CHARSET=utf8

if you haven't noticed based on this, I have no idea what I'm doing with the fulltext key, which may be the issue. Yet I did something similar the first time around and it works just fine.

This is my search query...

SELECT user_id, username, first_name, last_name,
 MATCH(username, first_name, last_name) AGAINST('" . $search . "') AS score
 FROM users
 WHERE MATCH(username, first_name, last_name) AGAINST('" . $search . "')
 ORDER BY username

Does anyone know what may be causing this issue? I've been at this for days and can't figure out the problem. Any help is appreciated, thank you.

Answer 1

You need a FULLTEXT key which looks like this:

FULLTEXT KEY `ft_index_thisoneiscorrect` (username, first_name, last_name)

(Note that the list of matched columns is the same as the list of columns you're matching in your query.)

Get rid of all the other ones. Most of them are useless, and several are outright dangerous.