fulltext searching

[Alex Glass]

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(firstNam e,'
',IFNULL(middle Name,''),' ',lastName) AS fullName, company, businessPhone,
contactType FROM contacts
WHERE MATCH
(fileAs,title,f irstName,middle Name,lastName,s uffix,jobTitle, company) AGAINST
('query_text_he re') OR
MATCH (baStreet,baCit y,baState,baPos tal,baCountry) AGAINST
('query_text_he re') OR
MATCH (haStreet,haCit y,haState,haPos tal,haCountry) AGAINST
('query_text_he re') OR
MATCH (oaStreet,oaCit y,oaState,oaPos tal,oaCountry) AGAINST
('query_text_he re') OR
MATCH (notes,email,co ntactType,websi te,salesPerson) AGAINST
('query_text_he re')
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`,`titl e`,`firstName`, `middleName`,`l astName`,`suffi x`,`jobTitle`,` company`),
FULLTEXT KEY `ftBAddress`
(`baStreet`,`ba City`,`baState` ,`baPostal`,`ba Country`),
FULLTEXT KEY `ftHAddress`
(`haStreet`,`ha City`,`haState` ,`haPostal`,`ha Country`),
FULLTEXT KEY `ftOAddress`
(`oaStreet`,`oa City`,`oaState` ,`oaPostal`,`oa Country`),
FULLTEXT KEY `ftOther`
(`notes`,`email `,`contactType` ,`website`,`sal esPerson`)
) 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