MySQL MATCH AGAINST when searching e-mail addresses

Question

I am writing a newsletter script and I need to implement searching in the addresses. I indexed the table with FULLTEXT but when I do a query such as:

SELECT * FROM addresses WHERE MATCH(email) AGAINST("[email protected]" IN BOOLEAN MODE)

I get strange results. It displays all emails on "example.com" and all emails with user "name". For example I get:

[email protected]
[email protected]
[email protected]

I rewrote the query to use LIKE "%[email protected]%" but for a big table it takes ridiculous amount of time to complete. Is there a solution for this? I want when searching to show only full matching emails and not part of them. Thank you in advance.

Justinejustinian

If you want an exact match to a column then why can't you just SELECT * FROM Addresses WHERE email = "[email protected]"? – Disoblige

Because on an 7M records table it takes 5 seconds to find it. With MATCH AGAINST it's a lot faster. – Justinejustinian

Give the email column a normal index to make the straight comparison query fast. There is no benefit in a fulltext index here. – Mayhew

Answer 1

To match an exact phrase you have to enclose the phrase in double quotes. So change your query to:

SELECT * FROM addresses WHERE MATCH(email) AGAINST('"[email protected]"' IN BOOLEAN MODE)

Source

Disoblige

I can't believe I didn't thought of that. Thank you, this solved my problem. – Justinejustinian

Seriously though, consider a SELECT on an indexed column. Should be much faster than a match. – Disoblige

Answer 2

You can use a traditional index (with a simple = operator) instead of a fulltext index. It will work fast and use less resource on DB engine.

SELECT * FROM addresses WHERE email = '[email protected]';

In this case, it is also possible to use a like operator, with % at the end of search string if you want to find by initial part of email address.

SELECT * FROM addresses WHERE email like 'name@exam%';
 

Jennette