4

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.

Vladimir
  • 818
  • 5
  • 13
  • 1
    If you want an exact match to a column then why can't you just `SELECT * FROM Addresses WHERE email = "name@example.com"`? – Jivings Jan 22 '12 at 12:53
  • Because on an 7M records table it takes 5 seconds to find it. With MATCH AGAINST it's a lot faster. – Vladimir Jan 22 '12 at 12:55
  • 2
    Give the email column a normal index to make the straight comparison query fast. There is no benefit in a fulltext index here. – bobince Jan 22 '12 at 13:45

2 Answers2

14

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

Jivings
  • 22,834
  • 6
  • 60
  • 101
0

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 = 'name@example.com';

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%';
Pedro Leite
  • 566
  • 7
  • 3