Page 1 of 1

problem with searching fulltext

Posted: Fri Jun 03, 2005 9:27 am
by thallish
hi everyone

i'm currently trying to get some fulltext search to work as i've heard it is a lot quicker then ordinary search (SELECT * FROM table WHERE field LIKE '%searchstring%').

First of all is this true and in which cases does this statement return true (to many hours at the code :lol:)

ok, now to my problem with some code:

Code: Select all

CREATE TABLE `employee` (
  `employeeId` smallint(6) NOT NULL auto_increment,
  `firstname` varchar(15) NOT NULL default '',
  `lastname` varchar(30) NOT NULL default '',
  `socialSkills` text NOT NULL,
  `desiredAreasOfWork` text NOT NULL,
  `competenceId` smallint(6) default '0',
  `isActivated` set('yes','no') NOT NULL default '',
  PRIMARY KEY  (`employeeId`),
  FULLTEXT KEY `socialSkills` (`socialSkills`),
  FULLTEXT KEY `desiredAreasOfWork` (`desiredAreasOfWork`)
) TYPE=MyISAM AUTO_INCREMENT=11 ;

Code: Select all

SELECT DISTINCT employee.employeeId,
                employee.firstname,
                employee.lastname
FROM		user,
		user_employee,
		employee
WHERE		MATCH(employee.socialSkills,employee.desiredAreasOfWork) AGAINST('$searchstring')
AND		employee.employeeId=user_employee.employeeId
AND		user_employee.userId=user.userId
AND		user.isActivated='yes'

Code: Select all

Can't find FULLTEXT index matching the column list
how come I get this error because as I can figure out my table does have the correct coloums set to full text?

Posted: Mon Jun 20, 2005 7:22 am
by []InTeR[]
You have 2 seperate indexes, they must be in 1 index.

Code: Select all

FULLTEXT KEY `socialSkills` (`socialSkills`,`desiredAreasOfWork`)
I think this will work.