webguync Posted April 1, 2011 Share Posted April 1, 2011 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? Link to comment https://forums.phpfreaks.com/topic/232403-how-to-see-which-columns-have-fulltext-index/ Share on other sites More sharing options...
webguync Posted April 1, 2011 Author Share Posted April 1, 2011 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. Link to comment https://forums.phpfreaks.com/topic/232403-how-to-see-which-columns-have-fulltext-index/#findComment-1195626 Share on other sites More sharing options...
fenway Posted April 3, 2011 Share Posted April 3, 2011 Your full-text index must match your MATCH..AGAINST exactly. Link to comment https://forums.phpfreaks.com/topic/232403-how-to-see-which-columns-have-fulltext-index/#findComment-1196250 Share on other sites More sharing options...
webguync Posted April 7, 2011 Author Share Posted April 7, 2011 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')"); Link to comment https://forums.phpfreaks.com/topic/232403-how-to-see-which-columns-have-fulltext-index/#findComment-1198280 Share on other sites More sharing options...
fenway Posted April 19, 2011 Share Posted April 19, 2011 Post your CREATE TABLE syntax. Link to comment https://forums.phpfreaks.com/topic/232403-how-to-see-which-columns-have-fulltext-index/#findComment-1203425 Share on other sites More sharing options...
webguync Posted April 19, 2011 Author Share Posted April 19, 2011 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 ; Link to comment https://forums.phpfreaks.com/topic/232403-how-to-see-which-columns-have-fulltext-index/#findComment-1203607 Share on other sites More sharing options...
webguync Posted April 21, 2011 Author Share Posted April 21, 2011 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 ; Link to comment https://forums.phpfreaks.com/topic/232403-how-to-see-which-columns-have-fulltext-index/#findComment-1204647 Share on other sites More sharing options...
fenway Posted April 24, 2011 Share Posted April 24, 2011 You can't mix FULLTEXT indexes -- if you want to search N columns, you'll need an index with N columns. Link to comment https://forums.phpfreaks.com/topic/232403-how-to-see-which-columns-have-fulltext-index/#findComment-1205579 Share on other sites More sharing options...
webguync Posted April 24, 2011 Author Share Posted April 24, 2011 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! Link to comment https://forums.phpfreaks.com/topic/232403-how-to-see-which-columns-have-fulltext-index/#findComment-1205615 Share on other sites More sharing options...
fenway Posted April 25, 2011 Share Posted April 25, 2011 Each of your FULLTEXT indexes lists a single column -- your MATCH() lists multiple. Link to comment https://forums.phpfreaks.com/topic/232403-how-to-see-which-columns-have-fulltext-index/#findComment-1205700 Share on other sites More sharing options...
webguync Posted April 25, 2011 Author Share Posted April 25, 2011 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 ; Link to comment https://forums.phpfreaks.com/topic/232403-how-to-see-which-columns-have-fulltext-index/#findComment-1205944 Share on other sites More sharing options...
Pikachu2000 Posted April 25, 2011 Share Posted April 25, 2011 Your query matches against 10 fields. Your FULLTEXT definition is spanning 14 fields. Link to comment https://forums.phpfreaks.com/topic/232403-how-to-see-which-columns-have-fulltext-index/#findComment-1205976 Share on other sites More sharing options...
webguync Posted April 25, 2011 Author Share Posted April 25, 2011 ok, Duh, thanks for catching that. Forgot to update my Query. Everything appears to be working now Link to comment https://forums.phpfreaks.com/topic/232403-how-to-see-which-columns-have-fulltext-index/#findComment-1206007 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.