mysql doesn’t like me! beware of mysql stopwords…

Today we spent few hours debugging search for friends functionality on one of our new projects. To make story short, we had one really complex fulltext search query. Interestingly enough, I was searching for my self (seven) in database, and I was unable to find ANY reference to user seven.

Empty set (31 ms):
SELECT * FROM t WHERE MATCH (name_nick ) AGAINST ('seven*' IN BOOLEAN MODE);

1 rows fetched (47 ms):
SELECT a FROM t WHERE name_nick like '%seven%';

1 rows fetched (47 ms):
SELECT * FROM t WHERE name_nick RLIKE '[[:<:]]seven[[:>:]]';

Be hold! MySQL Full-Text Stopwords.

You can get over this, by modifying mysql stopwords and recompiling from source (myisam/ft_static.c holds them), but I would rather not do that for now. We had to use fulltext search because like was just too damn slow for 5000+ users.

There is chance that this problem is to be fixed in future and it’s been tracked as initiative to remove Natural Language Full-Text Search limitations from boolean mode. Until then, if you want to be my friend… search for Neven! :)