MySQL Forums :: General :: FULLTEXT search failing


Advanced Search

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,
 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.

Options: ReplyQuote


Subject Written By Posted
FULLTEXT search failing Alan Little 11/10/2010 06:08AM
Re: FULLTEXT search failing Rick James 11/13/2010 01:19AM
Re: FULLTEXT search failing Alan Little 11/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.