There is a table has two fields as FULLTEXT index. When I perform select using MATCH it produces in sometimes multi repeated results for the same record. Why this is happned?
The following are :
Table Struture:
PHP Code:
CREATE TABLE `article` (
`id` int(5) unsigned zerofill NOT NULL auto_increment,
`typist_id` varchar(15) collate utf8_unicode_ci NOT NULL,
`author_name` varchar(40) collate utf8_unicode_ci NOT NULL,
`pub_date` datetime NOT NULL,
`write_date` datetime NOT NULL,
`title` varchar(255) collate utf8_unicode_ci NOT NULL,
`content` text collate utf8_unicode_ci NOT NULL,
`activity` tinyint(1) NOT NULL default '0',
`last_editor` varchar(15) collate utf8_unicode_ci NOT NULL,
`last_edit_date` datetime NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Table handles articles' AUTO_INCREMENT=16 ;
The query used
PHP Code:
SELECT id, pub_date, title, content FROM article WHERE MATCH(title,content) AGAINST('Some Search' IN BOOLEAN MODE) AND activity = 1 ORDER BY pub_date DESC
I forgot to mention that MySQL version is 4