[MySQL] Full Text search in Left joined tabellen

[Scout77]

Mysql zegt dat de volgende sql statement niet kan:

code:

SELECT REL_Identity.Firstname, REL_Identity.Lastname, REL_Address.Street, REL_Address.Zipcode, REL_Address.City, REL_Address.Country, REL_Internet.Emailaddress, REL_Internet.Website, REL_IM.MSN, REL_IM.Skype, REL_IM.IAM, REL_IM.Googletalk, REL_IM.Yahoo,
MATCH (
REL_Identity.Firstname, REL_Identity.Lastname, REL_Address.Street, REL_Address.Zipcode, REL_Address.City, REL_Address.Country, REL_Internet.Emailaddress, REL_Internet.Website, REL_IM.MSN, REL_IM.Skype, REL_IM.IAM, REL_IM.Googletalk, REL_IM.Yahoo
)
AGAINST (
'iets'
) AS score
FROM REL_Identity
LEFT JOIN REL_Address
USING ( ID )
LEFT JOIN REL_Internet
USING ( ID )
LEFT JOIN REL_IM
USING ( ID )
WHERE MATCH (
REL_Identity.Firstname, REL_Identity.Lastname, REL_Address.Street, REL_Address.Zipcode, REL_Address.City, REL_Address.Country, REL_Internet.Emailaddress, REL_Internet.Website, REL_IM.MSN, REL_IM.Skype, REL_IM.IAM, REL_IM.Googletalk, REL_IM.Yahoo
)
AGAINST (
'iets'
)
ORDER BY score DESC
LIMIT 0 , 30

En komt dan met de volgende melding op de proppen:
#1210 - Incorrect arguments to MATCH

mijn tabellen zien er zo uit:

code:

CREATE TABLE `REL_Identity` (
  `ID` tinyint(10) NOT NULL auto_increment,
  `Title` varchar(15) NOT NULL default '',
  `Initials` varchar(15) NOT NULL default '',
  `Firstname` varchar(15) NOT NULL default '',
  `Lastname` varchar(15) NOT NULL default '',
  `Gender` varchar(1) NOT NULL default '',
  `Photo` varchar(15) NOT NULL default '',
  `Changes` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  FULLTEXT(FirstName, LastName) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

CREATE TABLE `REL_Address` (
  `Address_ID` tinyint(10) NOT NULL auto_increment,
  `ID` tinyint(10) NOT NULL default '',
  `Street` varchar(75) NOT NULL default '',
  `Zipcode` varchar(7) NOT NULL default '',
  `City` varchar(75) NOT NULL default '',
  `Country` varchar(75) NOT NULL default '',
  `Telephone` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`Address_ID`),
  FULLTEXT(Street, Zipcode, City, Country, Telephone)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

CREATE TABLE `REL_Cellphone` (
  `Cellphone_ID` tinyint(10) NOT NULL auto_increment,
  `ID` tinyint(10) NOT NULL default '',
  `Cellphone` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`Cellphone_ID`),
  FULLTEXT(Cellphone)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

CREATE TABLE `REL_Employer` (
  `Employer_ID` tinyint(10) NOT NULL auto_increment,
  `ID` tinyint(10) NOT NULL default '',
  `Company` varchar(75) NOT NULL default '',
  `Street` varchar(75) NOT NULL default '',
  `Zipcode` varchar(75) NOT NULL default '',
  `PostBox` varchar(75) NOT NULL default '',
  `ReplyNumber` varchar(75) NOT NULL default '',
  `City` varchar(75) NOT NULL default '',
  `Country` varchar(75) NOT NULL default '',
  `EmailAddress` varchar(75) NOT NULL default '',
  PRIMARY KEY  (`Employer_ID`),
  FULLTEXT(Company, Street, Zipcode, PostBox, ReplyNumber, City, Country, Emailaddress)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

CREATE TABLE `REL_Anniversaries` (
  `Anniversaries_ID` tinyint(10) NOT NULL auto_increment,
  `ID` tinyint(10) NOT NULL default '',
  `Birthday` Date NOT NULL default '0000-00-00',
  `DyingDay` Date NOT NULL default '0000-00-00',
  `WeddingDay` Date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`Anniversaries_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

CREATE TABLE `REL_Family` (
  `Family_ID` tinyint(10) NOT NULL auto_increment,
  `ID` tinyint(10) NOT NULL default '',
  `Father` tinyint(10) NOT NULL default '',
  `Mother` tinyint(10) NOT NULL default '',
  `Partner` tinyint(10) NOT NULL default '',
  PRIMARY KEY  (`Family_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

CREATE TABLE `REL_Internet` (
  `Internet_ID` tinyint(10) NOT NULL auto_increment,
  `ID` tinyint(10) NOT NULL default '',
  `EmailAddress` varchar(75) NOT NULL default '',
  `Website` varchar(75) NOT NULL default '',
  PRIMARY KEY  (`Internet_ID`),
  FULLTEXT(Emailaddress, Website)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

CREATE TABLE `REL_IM` (
  `IM_ID` tinyint(10) NOT NULL auto_increment,
  `ID` tinyint(10) NOT NULL default '',
  `ICQ` Int(8) NOT NULL default '',
  `MSN` varchar(75) NOT NULL default '',
  `Skype` varchar(75) NOT NULL default '',
  `IAM` varchar(75) NOT NULL default '',
  `Googletalk` varchar(75) NOT NULL default '',
  `Yahoo` varchar(75) NOT NULL default '',
  PRIMARY KEY  (`IM_ID`),
  FULLTEXT(MSN, Skype, IAM, Googletalk, Yahoo)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

De join van de tabellen gaat goed. Iemand anders had ook het probleem ,hier, maar daar kon ook niemand iets zinnigs melden. Wel dat het met LIKE werkt maarja dat wil ik dus niet omdat dat met zo'n joined table helemaal langzaam werkt.

Iemand een idee wat er mis gaat ... of is het gewoon niet mogelijk om een full text search doen in een joined table?

[_js_]

Uit de MySQL handleiding:

The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE. Boolean-mode searches can be done on non-indexed columns, although they are likely to be slow.

Dus de match moet tegen 1 index zijn, en een index kan maar over 1 enkele tabel liggen.

[Scout77]

_js_ schreef op woensdag 23 augustus 2006 @ 11:15:
Uit de MySQL handleiding:
[...]
Dus de match moet tegen 1 index zijn, en een index kan maar over 1 enkele tabel liggen.

Tnx, IN BOOLEAN MODE, is de oplossing dan heb je die index niet meer nodig, moet wel iets langzamer gaan.

code:

SELECT REL_Identity.Firstname, REL_Identity.Lastname, REL_Address.Street, REL_Address.Zipcode, REL_Address.City, REL_Address.Country, REL_Address.Telephone, REL_Internet.Emailaddress, REL_Internet.Website, REL_IM.MSN, REL_IM.Skype, REL_IM.IAM, REL_IM.Googletalk, REL_IM.Yahoo,
MATCH (
REL_Identity.Firstname, REL_Identity.Lastname, REL_Address.Street, REL_Address.Zipcode, REL_Address.City, REL_Address.Country, REL_Address.Telephone, REL_Internet.Emailaddress, REL_Internet.Website, REL_IM.MSN, REL_IM.Skype, REL_IM.IAM, REL_IM.Googletalk, REL_IM.Yahoo
)
AGAINST (
'%iets%' IN BOOLEAN MODE
) AS score
FROM REL_Identity
LEFT JOIN REL_Address
USING ( ID )
LEFT JOIN REL_Internet
USING ( ID )
LEFT JOIN REL_IM
USING ( ID )
WHERE MATCH (
REL_Identity.Firstname, REL_Identity.Lastname, REL_Address.Street, REL_Address.Zipcode, REL_Address.City, REL_Address.Country, REL_Address.Telephone, REL_Internet.Emailaddress, REL_Internet.Website, REL_IM.MSN, REL_IM.Skype, REL_IM.IAM, REL_IM.Googletalk, REL_IM.Yahoo
)
AGAINST (
'%iets%' IN BOOLEAN MODE
)
ORDER BY score DESC

Dit geeft wel resultaat maar helaas nog niet elke zoekopdracht levert die resultaten op die het zou moeten opleveren.

edit -- Lijkt er op dat het probleem zit in het feit dat de joined table NULL waardes heeft.