Search code examples
mysqlsqlfull-text-searchfull-text-indexing

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("[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.

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('"[email protected]"' IN BOOLEAN MODE)

    Source