FULLTEXT search problems

[cheerio]

I can't figure out how to get full text search to work. This is my SQL structure:

CREATE TABLE `songs` (
  `songID` int(10) unsigned NOT NULL auto_increment,
  `title` text collate latin1_general_ci,
  `artist` text collate latin1_general_ci,
  `file` text collate latin1_general_ci,
  `downloads` int(11) NOT NULL,
  `category` text collate latin1_general_ci,
  `songDate` text collate latin1_general_ci,
  PRIMARY KEY  (`songID`),
  FULLTEXT KEY `title` (`title`,`artist`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

INSERT INTO `songs` VALUES (1, 'test', 'test', 'test', 23, 'test', '1157590502');

and this is my query:

SELECT * FROM `songs` WHERE MATCH (title,artist) AGAINST ('test' WITH QUERY EXPANSION)

I keep getting 0 results. Can anybody please point me in direction of HOPE!

[NiChE]

Maybe you should try something like this:

CREATE TABLE songs (
songID int(10) unsigned NOT NULL auto_increment,
title text collate latin1_general_ci,
artist text collate latin1_general_ci,
file text collate latin1_general_ci,
downloads int(11) NOT NULL,
category text collate latin1_general_ci,
songDate text collate latin1_general_ci,
PRIMARY KEY (songID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

CREATE FULLTEXT INDEX Index_Name ON songs(TITLE, ARTIST);

INSERT INTO songs VALUES (1, 'test', 'test', 'test', 23, 'test', '1157590502');

Notice that I removed the line: FULLTEXT KEY title (title,artist) on the table definition.

After that, try making your query.

By the way, check if the collation has something to do with your issue. 😉

[cheerio]

Edit: I figured out why it wasn't working. It's because my search terms were in more than 50% of the rows (meaning I didn't have enough rows, or my terms were too general)