MySQL Forums
Forum List  »  General

FULLTEXT search failing
Posted by: Alan Little
Date: November 10, 2010 06:08AM

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,
 FULLTEXT KEY `Search` (`Synopsis`),
 FULLTEXT KEY `SearchTitle` (`SearchTitle`)

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','')

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.

Options: ReplyQuote

Written By
FULLTEXT search failing
November 10, 2010 06:08AM
November 13, 2010 01:19AM
November 14, 2010 07:05PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.