[SOLVED] MySQL MATCH AGAINST when searching e-mail addresses

MySQL MATCH AGAINST when searching e-mail addresses

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("name@example.com" IN BOOLEAN MODE)

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

john@example.com
name@mail.net
steven@example.com

I rewrote the query to use LIKE "%name@example.com%" 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.

Solution

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('"name@example.com"' IN BOOLEAN MODE)

Source