MySQL SELECT rows that partly match a string from other TABLE

I have two tables: one with emails and the other table with domains.

Table1                  Table2
id ema开发者_开发问答il                id  domain
-- ----                 --  ----
1  name@domain1.com     1   domain1.com
2  name@domain2.com     2   domain4.com
3  name@domain3.com
4  name@domain4.com

Now I want to select all email from table 1 where the domain matches the domain field of table 2. The result should be:

id email
-- ----
1  name@domain1.com
4  name@domain4.com

I guess it would work with a combination of REGEXP and INNER JOIN? But I don't know how to combine them.

I created to tables and made a test, and this query worked for me:

SELECT t1.*, t2.domain FROM t1
INNER JOIN t2 ON t1.email LIKE CONCAT('%@', t2.domain);

SOlution 1 :

Use Table1.email LIKE CONCAT('%@',Table2.domain).

BIG FAT RED WARNING : This condition is not indexable. If you want speed, split the email into address and domain in Table1, and create an index.

EDIT :

In fact this JOIN condition will need count(email)xcount(domain) comparisons, so it's probably the slowest way.

Solution 2:

Rob has a very good point : a faster way is to extract the domain from the email (using substring) and match this against an index on the domains table

SOlution 3 :

The best way is to index the email's domain. In Postgres you would create a function index ON extract_domain(email) (supposing you create a trivial extract_domain() function), but you cannot do this in MySQL, so an extra column with the domain only is the way to go for speed.

If you want to know all emails in ONE domain,

Solution 1 : seq scan table emails + fast LIKE
Solution 2 : seq scan table emails + slightly slower domain extraction
Solution 3 : index scan table emails

If you want to JOIN on table domains for all domains / all emails :

Solution 1 : count(email)xcount(domain) comparisons, very slow
Solution 2 : seq scan table emails + index scan domains
Solution 3 : nested loop index join

For a full JOIN it would be even faster to use a merge join or hash join, but those are not provided by mysql.

Try this out

SELECT t1.Id, 
       t1.Email
  FROM Table1 t1
  JOIN Table2 t2 ON t1.email LIKE CONCAT('%@', t2.Domain);