Mysql Matching “Same” Emails

I have a table with 2 columns email and id. I need to find emails that are closely related. For example:

email

id

john.smith12@example.com


and

john.smith12@some.subdomains.example.com


These should be considered the same because the username (john.smith12) and the most top level domain (example.com) are the same. They are currently 2 different rows in my table. I've written the below expression which should do that comparison but it takes hours to execute (possibly/probably because of regex). Is there a better way to write this:

john.smith12

example.com

select c1.email, c2.email
from table as c1
join table as c2
on (
c1.leadid <> c2.leadid
and
c1.email regexp replace(replace(c2.email, '.', '[.]'), '@', '@[^@]*'))


The explain of this query comes back as:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, c1, ALL, NULL, NULL, NULL, NULL, 577532, NULL
1, SIMPLE, c2, ALL, NULL, NULL, NULL, NULL, 577532, Using where; Using join buffer (Block Nested Loop)


The create table is:

CREATE TABLE `table` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Email` varchar(100) DEFAULT NULL,
KEY `Table_Email` (`Email`),
KEY `Email` (`Email`)
) ENGINE=InnoDB AUTO_INCREMENT=667020 DEFAULT CHARSET=latin1


I guess the indices aren't being used because of the regexp.

The regex comes out as:

john[.]smith12@[^@]*example[.]com


which should match both addresses.

Update:

I've modified the on to be:

on

on (c1.email <> '' and c2.email <> '' and c1.leadid <> c2.leadid and substr(c1. email, 1, (locate('@', c1.email) -1)) = substr(c2. email, 1, (locate('@', c2.email) -1))
and
substr(c1.email, locate('@', c1.email) + 1) like concat('%', substr(c2.email, locate('@', c2.email) + 1)))


and the explain with this approach is at least using the indices.

explain

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, c1, range, table_Email,Email, table_Email, 103, NULL, 288873, Using where; Using index
1, SIMPLE, c2, range, table_Email,Email, table_Email, 103, NULL, 288873, Using where; Using index; Using join buffer (Block Nested Loop)


So far this has executed for 5 minutes, will update if there is a vast improvement.

%

LIKE

john.smith@%

WHERE c1.email LIKE CONCAT(SUBSTR(c2.email, 1, POSITION(c2.email, '@')), '%') AND ...

1 Answer
1

No REGEXP_REPLACE needed, so it will work in all versions of MySQL/MariaDB:

REGEXP_REPLACE

UPDATE tbl
SET email = CONCAT(SUBSTRING_INDEX(email, '@', 1),
'@',
SUBSTRING_INDEX(
SUBSTRING_INDEX(email, '@', -1),
'.',
-2);


Since no index is useful, you may as well not bother with a WHERE clause.

WHERE

%

)

-3

CONCAT(SUBSTRING_INDEX(email, '@', 1), '@', SUBSTRING_INDEX( SUBSTRING_INDEX(email, '@', -1), '.', -3));

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.