I have a large contacts table with about 30 columns of text fields and 5
fulltext indexes spanning the different sections of the table. I'm curious
if anyone could suggest a better way to find rows in the table based on text
entered by the user. It also would be nice if wildcards could be supported
like "beginning*". The query I have built crashed mysqlnt-d a few times but
I was unable to determine what triggered the crash. I believe it has
something to do with the fact that I'm improperly using the fulltext index
feature. Anyhow the query is below.
-------------------------------------------------------------------------------------------------
Fulltext Query:
SELECT DISTINCT contacts.id, fileAs, CONCAT(firstName,'
',IFNULL(middleName,''),' ',lastName) AS fullName, company, businessPhone,
contactType FROM contacts
WHERE MATCH
(fileAs,title,firstName,middleName,lastName,suffix ,jobTitle,company) AGAINST
('query_text_here') OR
MATCH (baStreet,baCity,baState,baPostal,baCountry) AGAINST
('query_text_here') OR
MATCH (haStreet,haCity,haState,haPostal,haCountry) AGAINST
('query_text_here') OR
MATCH (oaStreet,oaCity,oaState,oaPostal,oaCountry) AGAINST
('query_text_here') OR
MATCH (notes,email,contactType,website,salesPerson) AGAINST
('query_text_here')
ORDER BY fileAs ASC
LIMIT 0,500
------------------------------------------------------------------------------------------------------
Source code for the contacts table:
CREATE TABLE `contacts` (
`id` int(3) NOT NULL auto_increment,
`artistId` int(3) NOT NULL default '0',
`fileAs` varchar(255) NOT NULL default '',
`title` varchar(15) default NULL,
`firstName` varchar(50) default NULL,
`middleName` varchar(50) default NULL,
`lastName` varchar(50) default NULL,
`suffix` varchar(15) default NULL,
`jobTitle` varchar(50) default NULL,
`company` varchar(50) default NULL,
`homePhone` varchar(50) default NULL,
`businessPhone` varchar(50) default NULL,
`mobilePhone` varchar(50) default NULL,
`faxPhone` varchar(50) default NULL,
`baStreet` varchar(100) default NULL,
`baCity` varchar(50) default NULL,
`baState` varchar(50) default NULL,
`baPostal` varchar(20) default NULL,
`baCountry` varchar(50) default NULL,
`haStreet` varchar(100) default NULL,
`haCity` varchar(50) default NULL,
`haState` varchar(50) default NULL,
`haPostal` varchar(20) default NULL,
`haCountry` varchar(50) default NULL,
`oaStreet` varchar(100) default NULL,
`oaCity` varchar(50) default NULL,
`oaState` varchar(50) default NULL,
`oaPostal` varchar(20) default NULL,
`oaCountry` varchar(50) default NULL,
`mailingAddress` tinyint(3) default NULL,
`notes` text,
`email` varchar(100) default NULL,
`contactType` varchar(50) default NULL,
`website` varchar(150) default NULL,
`entryId` varchar(100) default NULL,
`balance` decimal(8,2) NOT NULL default '0.00',
`salesPerson` varchar(50) default NULL,
`listid` varchar(50) default NULL,
`created` date default NULL,
`updated` date default NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `ftContact`
(`fileAs`,`title`,`firstName`,`middleName`,`lastNa me`,`suffix`,`jobTitle`,`company`),
FULLTEXT KEY `ftBAddress`
(`baStreet`,`baCity`,`baState`,`baPostal`,`baCount ry`),
FULLTEXT KEY `ftHAddress`
(`haStreet`,`haCity`,`haState`,`haPostal`,`haCount ry`),
FULLTEXT KEY `ftOAddress`
(`oaStreet`,`oaCity`,`oaState`,`oaPostal`,`oaCount ry`),
FULLTEXT KEY `ftOther`
(`notes`,`email`,`contactType`,`website`,`salesPer son`)
) TYPE=MyISAM;
================================================== ===================================
I unfortunately had to create 5 different fulltext indexes on the table
because there is a maximum amount of columns the server would allow me to
add per index.. I considered using LIKE queries but they are extremely slow
and unrealistic to use when dealing with this many fields. The server
version I am using is MySql 4.0.18. you can view a picture of the contacts
table structure with the indexes here
http://www.artdt.com/misc/contact_table.gif
Thanks for any tips or help,
Alex
