how to see which columns have FULLTEXT index

[webguync]

I have a search application which searches info in a MySQL table. It was working, but then I added some more columns and now get the following error in the SQL.

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

 

I thought I had made all of the columns FULLTEXT Anyway to show which columns aren't fulltext?

[webguync]

well I found out how to get a list of the columns indexed which is with this SQL

select group_concat(distinct column_name)
from information_schema.STATISTICS 
where table_schema = 'your_db' 
and table_name = 'your_table' 
and index_type = 'FULLTEXT';

 

I am still frustrated though, because all of my columns show as being FULLTEXT, but I can get the SQL error.

 

[fenway]

Your full-text index must match your MATCH..AGAINST exactly.

[webguync]

I believe it does. Here is the PHP code

$result=$db->query("SELECT last_name, first_name,employee_id,title,territory,district,Phase1A_Score,Phase1B_Score,Phase1_Average,Class_Date FROM
Phase1A_1B_TotalScores_2011 WHERE MATCH(last_name, first_name,employee_id,title,territory,district,Phase1A_Score,Phase1B_Score,Phase1_Average,Class_Date) AGAINST
('$searchterm')");

[fenway]

Post your CREATE TABLE syntax.

[webguync]

I think I might see the problem, it looks like some of the fields were made FULLTEXT twice or three times, creating some unwanted duplicates, so I will need to undo some of the FULLTEXT. How do I do that?

 

SQL below

 

CREATE TABLE `Phase1A_1B_TotalScores_2011` (
  `last_name` text,
  `first_name` text,
  `employee_id` varchar(5) default NULL,
  `title` varchar(10) default NULL,
  `territory` varchar(255) default NULL,
  `district` varchar(255) default NULL,
  `Phase1A_Score` varchar(255) default NULL,
  `Phase1B_Score` varchar(255) default NULL,
  `Phase1_Average` varchar(255) default NULL,
  `Phase1A_HS_Exam` varchar(255) default NULL,
  `Phase1A_HS_Exam_RT` varchar(255) NOT NULL,
  `Phase1B_HS_Exam` varchar(255) NOT NULL,
  `Phase1B_HS_Exam_RT` varchar(255) NOT NULL,
  `Class_Date` varchar(255) NOT NULL,
  `roster_id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`roster_id`),
  FULLTEXT KEY `last_name` (`last_name`),
  FULLTEXT KEY `SearchMe` (`last_name`,`first_name`,`employee_id`,`title`,`territory`,`district`,`Phase1A_Score`,`Phase1B_Score`,`Phase1_Average`,`Class_Date`),
  FULLTEXT KEY `Phase1A_HS_Exam` (`Phase1A_HS_Exam`),
  FULLTEXT KEY `last_name_2` (`last_name`),
  FULLTEXT KEY `first_name` (`first_name`),
  FULLTEXT KEY `employee_id` (`employee_id`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `territory` (`territory`),
  FULLTEXT KEY `district` (`district`),
  FULLTEXT KEY `Phase1A_Score` (`Phase1A_Score`),
  FULLTEXT KEY `Phase1B_Score` (`Phase1B_Score`),
  FULLTEXT KEY `Phase1_Average` (`Phase1_Average`),
  FULLTEXT KEY `Class_Date` (`Class_Date`),
  FULLTEXT KEY `district_2` (`district`),
  FULLTEXT KEY `territory_2` (`territory`),
  FULLTEXT KEY `title_2` (`title`),
  FULLTEXT KEY `employee_id_2` (`employee_id`),
  FULLTEXT KEY `Phase1A_HS_Exam_2` (`Phase1A_HS_Exam`),
  FULLTEXT KEY `Phase1_Average_2` (`Phase1_Average`),
  FULLTEXT KEY `Class_Date_2` (`Class_Date`),
  FULLTEXT KEY `Phase1B_HS_Exam` (`Phase1B_HS_Exam`),
  FULLTEXT KEY `Phase1_Average_3` (`Phase1_Average`)
) ENGINE=MyISAM AUTO_INCREMENT=289 DEFAULT CHARSET=utf8 AUTO_INCREMENT=289 ;

[webguync]

hmm, I edited my SQL and took out the duplicated and tried again. Still get the same error. It looks to me like everything matches up?

 

CREATE TABLE `Phase1A_1B_TotalScores_2011_1` (
  `last_name` text,
  `first_name` text,
  `employee_id` varchar(5) default NULL,
  `title` varchar(10) default NULL,
  `territory` varchar(255) default NULL,
  `district` varchar(255) default NULL,
  `Phase1A_Score` varchar(255) default NULL,
  `Phase1B_Score` varchar(255) default NULL,
  `Phase1_Average` varchar(255) default NULL,
  `Phase1A_HS_Exam` varchar(255) default NULL,
  `Phase1A_HS_Exam_RT` varchar(255) NOT NULL,
  `Phase1B_HS_Exam` varchar(255) NOT NULL,
  `Phase1B_HS_Exam_RT` varchar(255) NOT NULL,
  `Class_Date` varchar(255) NOT NULL,
  `roster_id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`roster_id`),
  FULLTEXT KEY `last_name` (`last_name`),
  FULLTEXT KEY `first_name` (`first_name`),
  FULLTEXT KEY `employee_id` (`employee_id`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `territory` (`territory`),
  FULLTEXT KEY `district` (`district`),
  FULLTEXT KEY `Phase1A_Score` (`Phase1A_Score`),
  FULLTEXT KEY `Phase1B_Score` (`Phase1B_Score`),
  FULLTEXT KEY `Phase1_Average` (`Phase1_Average`),
  FULLTEXT KEY `Phase1A_HS_Exam` (`Phase1A_HS_Exam`),
  FULLTEXT KEY `Phase1A_HS_Exam_RT` (`Phase1A_HS_Exam_RT`),
  FULLTEXT KEY `Phase1B_HS_Exam` (`Phase1B_HS_Exam`),
  FULLTEXT KEY `Phase1B_HS_Exam_RT` (`Phase1B_HS_Exam_RT`),
  FULLTEXT KEY `Class_Date` (`Class_Date`)
) ENGINE=MyISAM AUTO_INCREMENT=289 DEFAULT CHARSET=utf8 AUTO_INCREMENT=289 ;

[fenway]

You can't mix FULLTEXT indexes -- if you want to search N columns, you'll need an index with N columns.

[webguync]

I don't see where I am mixing FULLTEXT indexes. Can you explain that more and post some SQL code to index N columns?

 

thanks for your help!

[fenway]

Each of your FULLTEXT indexes lists a single column -- your MATCH() lists multiple.

[webguync]

I changed the SQL but still get the same error. Please explain what I am still doing wrong. SQL code below.

 

CREATE TABLE `Phase1A_1B_TotalScores_2011_1` (
  `last_name` text,
  `first_name` text,
  `employee_id` varchar(5) default NULL,
  `title` varchar(10) default NULL,
  `territory` varchar(255) default NULL,
  `district` varchar(255) default NULL,
  `Phase1A_Score` varchar(255) default NULL,
  `Phase1B_Score` varchar(255) default NULL,
  `Phase1_Average` varchar(255) default NULL,
  `Phase1A_HS_Exam` varchar(255) default NULL,
  `Phase1A_HS_Exam_RT` varchar(255) NOT NULL,
  `Phase1B_HS_Exam` varchar(255) NOT NULL,
  `Phase1B_HS_Exam_RT` varchar(255) NOT NULL,
  `Class_Date` varchar(255) NOT NULL,
  `roster_id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`roster_id`),
FULLTEXT KEY (`last_name`,`first_name`,`employee_id`,`title`,`territory`,`district`,`Phase1A_Score`,`Phase1B_Score`,`Phase1_Average`,`Phase1A_HS_Exam`,`Phase1A_HS_Exam_RT`,`Phase1B_HS_Exam`,`Phase1B_HS_Exam_RT`,`Class_Date`)
) ENGINE=MyISAM AUTO_INCREMENT=289 DEFAULT CHARSET=utf8 AUTO_INCREMENT=289 ;

[Pikachu2000]

Your query matches against 10 fields. Your FULLTEXT definition is spanning 14 fields.

[webguync]

ok, Duh, thanks for catching that. Forgot to update my Query. Everything appears to be working now