-1

I have a table. The query of the table as follows

CREATE TABLE IF NOT EXISTS `Table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `univ_id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `content` mediumtext NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `content` (`content`),
  FULLTEXT KEY `content_2` (`content`),
  FULLTEXT KEY `title_2` (`title`),
  FULLTEXT KEY `title_3` (`title`),
  FULLTEXT KEY `title_4` (`title`),
  FULLTEXT KEY `content_3` (`content`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=36 ;

Has a title and content. When i am trying to do a search.

SELECT * FROM table WHERE MATCH(title) AGAINST('HAi')

It is giving expected result When i am trying to do a search

SELECT * FROM table WHERE MATCH(content) AGAINST('HAi')

It is giving expected result But When i am trying to do a search like this

SELECT * FROM table WHERE MATCH(title,content) AGAINST('HAi')

It is showing

#1191 - Can't find FULLTEXT index matching the column list
Neenu
  • 47
  • 1
  • 14
  • I tried ALTER TABLE ADD FULLTEXT title(title,content); Not worked then i tried For every column then also the above error is coming out.
     – Neenu Aug 14 '14 at 06:28
  • #1061 - Duplicate key name 'title' This error is coming out when i am trying to do above mentioned change. – Neenu Aug 14 '14 at 06:30
  • Can you please add the create table statement? – Jens Aug 14 '14 at 06:32
  • CREATE TABLE IF NOT EXISTS `table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `univ_id` int(11) NOT NULL, `title` varchar(100) NOT NULL, `content` mediumtext NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `title` (`title`), FULLTEXT KEY `content` (`content`), FULLTEXT KEY `content_2` (`content`), FULLTEXT KEY `title_2` (`title`), FULLTEXT KEY `title_3` (`title`), FULLTEXT KEY `title_4` (`title`), FULLTEXT KEY `content_3` (`content`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=36 ; – Neenu Aug 14 '14 at 06:36

1 Answers1

0

Try to recreate the table with the following statement:

   CREATE TABLE IF NOT EXISTS `table` ( 
  id int(11) NOT NULL AUTO_INCREMENT, 
  univ_id int(11) NOT NULL, 
  title varchar(100) NOT NULL, 
  content mediumtext NOT NULL, 
  date date NOT NULL, PRIMARY KEY (id), 
  FULLTEXT KEY title1 (title),
  FULLTEXT KEY content (content),
  FULLTEXT KEY title (title, content)
   ) 
ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=36

Then your selectshould running well.

Jens
  • 67,715
  • 15
  • 98
  • 113