The record I'm trying to find is #13, searching 'alone' on the SearchTitle column, and it's not finding it. The search also fails with 'novel' and 'alone novel', but works with 'suspense', by itself or added to the other search terms. All other searches on this table work as expected. [Edit: there are others which fail unexpectedly, but this one demonstrates the issue] Any ideas?
Table definition:
CREATE TABLE `books` (
`BookID` int(11) unsigned NOT NULL auto_increment,
`Title` varchar(255) default NULL,
`Author` varchar(128) default NULL,
`ItemCode` varchar(64) default NULL,
`Genre` tinyint(4) default NULL,
`SearchTitle` text,
`Synopsis` text,
PRIMARY KEY (`BookID`),
FULLTEXT KEY `Search` (`Synopsis`),
FULLTEXT KEY `SearchTitle` (`SearchTitle`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
Values:
insert into books values
('1','And the Devil Will Drag You Under','Jack L. Chalker','0345323343','2','devil will drag you under',''),
('2','Songmaster','Orson Scott Card','0312876629','1','songmaster',''),
('3','Silver Canyon: A Novel','Louis L\'Amour','0553247433','4','silver canyon novel',''),
('4','Macroscope','Piers Anthony','097236708X','1','macroscope',''),
('5','Emergence','David R. Palmer','B002U4W1QA','1',NULL,''),
('6','To Spite the Devil','Paula Jones','0786000309',NULL,NULL,''),
('7','A Wrinkle in Time','Madeleine L\'Engle','0028180054',NULL,'wrinkle time',''),
('8','Way Of The Pilgrim','Gordon R. Dickson','044187486X',NULL,'way pilgrim',''),
('14','Meg: Hell\'s Aquarium','Steve Alten','0765365855',NULL,'meg hells aquarium',''),
('13','Alone: A Novel of Suspense','Lisa Gardner','0553584537',NULL,'alone novel suspense',''),
('12','Eight Days to Live: An Eve Duncan Forensics Thriller','Iris Johansen','0312368143',NULL,'eight days live VBDEF duncan forensics thriller',''),
('15','Angel Time (The Songs of the Seraphim)','Anne Rice','1400078954',NULL,'angel time songs seraphim','')
Queries:
select count(distinct BookID) from books where match (SearchTitle) against ('alone' IN BOOLEAN MODE)
select ItemCode, match (SearchTitle) against ('alone' IN BOOLEAN MODE) Score from books where match (SearchTitle) against ('alone' IN BOOLEAN MODE) order by Score desc limit 0, 10
Edited 1 time(s). Last edit at 11/10/2010 08:30AM by Alan Little.