Strange results when doing a Fuzzy comparison of two email addresses with the match/against function

[David Usherwood]

I trying to do a fuzzy comparison of two emails adddress in a table with approx 2000 recs using the match/against function

1). I have a table with two email addresses and a centre code namely:

CREATE TABLE IF NOT EXISTS `compareemailaddress` (
`PHPEmailAddress` varchar(50) NOT NULL,
`NSEmailAddress` varchar(50) NOT NULL,
`NSCentreID` int(5) NOT NULL,
FULLTEXT KEY `NSEmailAddress` (`NSEmailAddress`),
FULLTEXT KEY `PHPEmailAddress` (`PHPEmailAddress`),
FULLTEXT KEY `PHPEmailAddress_2` (`PHPEmailAddress`,`NSEmailAddress`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

As you can I have added what I think are the relevant full text indexes

This is my data:

INSERT INTO `compareemailaddress` (`PHPEmailAddress`, `NSCentreID`, `NSEmailAddress`, `Williamclintonarizonaacouk`) VALUES
('[e-mail]', 1, '[e-mail]', 0),
('[e-mail]', 2, '[e-mail]', 0),
('[e-mail]', 3, '[e-mail]', 0),
('[e-mail]', 4, '[e-mail]', 0);


The issue that I am having is that sometimes I get a relevance result back and sometime I do not! Aa an aside can anyone advise what the relevance integer means ? Does the biiger the positive number mean a more precise match ?


So when I run this code: Do I need to use the IN BOOLEAN mode ?

SELECT PHPEmailAddress, NSCentreID, NSEmailAddress,
MATCH NSEmailAddress
AGAINST (
'[e-mail]'
) AS Relevance
FROM compareemailaddress AS cea

So I am comparing '[e-mail]'

against

'[e-mail]'

I get a relevance match of 1.06197416782379 - great this tells me its a good partial match


However, if I send

SELECT PHPEmailAddress, NSCentreID, NSEmailAddress,
MATCH NSEmailAddress
AGAINST (
'[e-mail]'
) AS Relevance
FROM compareemailaddress AS cea

So if I am comparing '[e-mail]'

against

'[e-mail]'

I get a relevance match of 0 (zero) - surely this should also be a good partial match ??


Is there a problem with my code here or does the match/against function not work properly?

Many thanks for your help