problem with searching fulltext

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
thallish
Forum Commoner
Posts: 60
Joined: Wed Mar 02, 2005 11:38 am
Location: Aalborg, Denmark

problem with searching fulltext

Post 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?
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post 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.
Post Reply