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


Advanced Search

Strange results when doing a Fuzzy comparison of two email addresses with the match/against function
Posted by: David Usherwood ()
Date: January 08, 2011 04:03PM

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
('abe.lincoln@usa.com', 1, 'abrahamlincoln@usa.org', 0),
('JamesCarter@whitehouse.co.uk', 2, 'jimmycarter@whitehouse.com', 0),
('BillClinton@arizona.com', 3, 'Bill.Clinton@arizona.com', 0),
('GeraldFord@Texas.com', 4, 'Gford@texas.co.uk', 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 (
'BillClinton@arizona.com'
) AS Relevance
FROM compareemailaddress AS cea

So I am comparing 'BillClinton@arizona.com'

against

'Bill.Clinton@arizona.com'

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 (
'BillClinton@arizonaa.com'
) AS Relevance
FROM compareemailaddress AS cea

So if I am comparing 'BillClinton@arizonaa.com'

against

'Bill.Clinton@arizona.com'

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

Options: ReplyQuote


Subject Written By Posted
Strange results when doing a Fuzzy comparison of two email addresses with the match/against function David Usherwood 01/08/2011 04:03PM
Re: Strange results when doing a Fuzzy comparison of two email addresses with the match/against function Rick James 01/09/2011 06:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.