Mysql – Full text indexing not working + Mysql 5.6.4

MySQL

CREATE TABLE `table_copy` (
  `link_id` int(11) NOT NULL AUTO_INCREMENT,
  `fulltxt` mediumtext,
  `indexdate` date DEFAULT NULL,
  `size` float DEFAULT NULL,
  `visible` int(11) DEFAULT '0',
  `level` int(11) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`link_id`),
  KEY `indexdate` (`indexdate`),
  FULLTEXT KEY `fulltext2` (`fulltxt`)
) ENGINE=INNODB AUTO_INCREMENT=274212 DEFAULT CHARSET=latin1

The above table contains about 2,74,211 records, when i tried to get data from table it takes 2 minutes to execute.

Query: SELECT c.link_id from table_copy c where MATCH(fulltxt) AGAINST('Council')

How can i optimize the above query?

Best Answer

You need check - configuration of server, memory for InnoDB, for FullText

compare size of index and FT buffer size, by default it only 8Mb, max value is 80Mb

Than next step and profile query - how many records returned, what time of intialyze of index

and upgrade 5.6.4 to something more new in 5.6.4 FullText was only implement for InnoDB, many bugs fixed from that time

Related Question