Dear EE members,
I am at a loss here so if anyone can help I would greatly appreciate it!
I have a MySQL server v4.1.21 out of my control (run by a hosting company).
CREATE TABLE `contacts` (
`TransID` bigint(15) unsigned zerofill NOT NULL default '000000000000000',
`FName` varchar(20) NOT NULL default '',
`LName` varchar(30) NOT NULL default '',
`Add1` varchar(254) NOT NULL default '',
`City` varchar(45) NOT NULL default '',
`State` char(2) NOT NULL default '',
`ZIP` varchar(10) NOT NULL default '',
PRIMARY KEY (`TransID`),
KEY `INDEX` (`LName`,`Add1`),
FULLTEXT KEY `FULLTEXT` (`Add1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I currently have about 20K entries.
I am really only having trouble finding one entry, but I am concerned that this may not be isolated.
I am running the following query:
SELECT `TransID` FROM `contacts`
WHERE (
`FName` = 'john'
AND `LName` = 'doe'
AND MATCH (`Add1`) AGAINST ('"6" oak street' IN BOOLEAN MODE)
AND `City` = 'Sometown'
AND `State` = 'PA'
AND `ZIP` = '17356'
) ORDER BY `TransID` DESC
** The query is modified to protect personal data **
I had decided in the design phase to use MATCH AGAINST so that we can accomidate for 'user mistakes' between the data and the searching form. For example, the database will most likely have the USPS style of address '6 OAK ST.' and the user may search '6 oak street'. Normal LIKE will fail here unless I try to be smart and remove common words like 'street', 'avenue', etc.
It seemed like everyone worked without a hitch, but this one query fails to find any results if the `Add1` MATCH is left in the query.
Even when I query as simple as "SELECT * FROM `contacts`WHERE MATCH (`Add1`) AGAINST ('6 oak' IN BOOLEAN MODE)" I get no results, but if I do a manual LIKE statement there are two '6 oak' entries, one at a '6 oak street' and one at a '6 oak lane'.
Am I maybe using this the wrong way? Is there a better method?
I am concerned that MySQL is not indexing it becuase '6' and 'oak' are so common in addresses...
Thanks in advance! -Cheers, Peter.
Start Free Trial
