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! :)
September 15, 2009 @ 11:17
Pingback bling bling nivas.hr blog » Blog Archive » How to disable mysql fulltext stopwords? says:
[...] A while ago, I ranted about fulltext stopwords in [...]