CODEWITHSUNDEEP
mysqlfull-text-search
Marius
Marius

Reputation: 4016

MySQL fulltext does not search for short emails

I have a fulltext index on a number of columns and i'm trying to do a MATCH AGAINST IN BOOLEAN MODE on those columns, trying to find an email address. Here are the results:

Can someone tell me why a short email [email protected] does not get returned and how would i solve this?

Here's the query i'm using:

SELECT MATCH(email, phone, title, description) AGAINST('"[email protected]"' IN BOOLEAN MODE) AS score 
FROM thetable WHERE MATCH(email, phone, title, description) 
AGAINST('"[email protected]"' IN BOOLEAN MODE) ORDER BY `status` DESC, score DESC

Upvotes: 0

Views: 231

Answers (2)

metalfight - user868766
metalfight - user868766

Reputation: 2750

Update:

a)You need to set ft_min_word_len = 1 in my.cnf.

b) Output of show variables

ft_min_word_len                                   | 1

c) Fired below query:

mysql> SELECT name,email FROM jos_users WHERE MATCH (email) AGAINST ('[email protected]') limit 1;
 +--------+---------+
 | name   | email   |
 +--------+---------+
 | kap | [email protected] |
 +--------+---------+
 1 row in set (0.00 sec)

Hope this will help.

~K

I think you need to change ft_min_word_len

As specified in MySQLdoc fine tuning

The minimum and maximum lengths of words to be indexed are defined by the ft_min_word_len and ft_max_word_len system variables. (See Section 5.1.4, “Server System Variables”.) The default minimum value is four characters; the default maximum is version dependent. If you change either value, you must rebuild your FULLTEXT indexes. For example, if you want three-character words to be searchable, you can set the ft_min_word_len variable by putting the following lines in an option file:

[mysqld] ft_min_word_len=3 Then restart the server and rebuild your FULLTEXT indexes. Note particularly the remarks regarding myisamchk in the instructions following this list.

Upvotes: 0

Hartmut Holzgraefe
Hartmut Holzgraefe

Reputation: 2765

This is a combination of two problems:

  1. @ isn't considered being a 'word character', and neither is -, so searching for [email protected] actually comes down to searching for words a, b and com
  2. a and b are shorter than ft_min_word_len

The solution would be to make @ and . being considered word characters. There are several methods listed on http://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html

The most practical one would be adding a custom collation as described in

http://dev.mysql.com/doc/refman/5.6/en/full-text-adding-collation.html

Upvotes: 1

Related Questions