MySQL fulltext search always empty

If you’ve just switched to FULLTEXT indexes and you’ve just started playing with MATCH… AGAINST syntax, but you always get…

November 23, 2013

mysql fulltext index errors

If you’ve just switched to FULLTEXT indexes and you’ve just started playing with MATCH... AGAINST syntax, but you always get 0 rows results, probably you’re not crazy, and no, it isn’t a mysql bug. There are some common mistakes that developers make:

MYSQL FULLTEXT INDEX ERRORS

1 – check if you’re searching short words, by default mySQL fulltext indexes skip words long less than 4 letters. If you search “+get +ready”, get is skipped. To change this limit you have to modify the mysql configuration file (probably you can ask your server support to find the file and modify it) and change the ft_min_word_len parameter. For example, put: ft_min_word_len=3

2 – check the stop words list. MySQL has a stop words list, this list blocks common words that are skipped. If you search +now +available the word available is skipped!!! Down you can find the list of default stop words.
To remove the stop words list and search every word, you have to modify your mysql configuration and put ft_stopword_file = '' or let it link an empty txt file ft_stopword_file = 'nowrods.txt'. Remember to restart mysql and rebuild indexes with REPAIR command (you can see syntax in the next paragraph).

3 – if you have a table full of rows and you add the fulltext index, the index you’ve just added is empty! Remember that the index is built when you add or update rows, to build or rebuild the index you’ve to use REPAIR command: REPAIR TABLE your_table_name QUICK;

FULLTEXT STOPWORDS LIST

As you can see in this list there are a lot of terms that you should want to be indexed:

a’s, able, about, above, according, accordingly, across, actually, after, afterwards, again, against, ain’t, all, allow, allows, almost, alone, along, already, also, although, always, am, among, amongst, an, and, another, any, anybody, anyhow, anyone, anything, anyway, anyways, anywhere, apart, appear, appreciate, appropriate, are, aren’t, around, as, aside, ask, asking, associated, at, available, away, awfully, be, became, because, become, becomes, becoming, been, before, beforehand, behind, being, believe, below, beside, besides, best, better, between, beyond, both, brief, but, by, c’mon, c’s, came, can, can’t, cannot, cant, cause, causes, certain, certainly, changes, clearly, co, com, come, comes, concerning, consequently, consider, considering, contain, containing, contains, corresponding, could, couldn’t, course, currently, definitely, described, despite, did, didn’t, different, do, does, doesn’t, doing, don’t, done, down, downwards, during, each, edu, eg, eight, either, else, elsewhere, enough, entirely, especially, et, etc, even, ever, every, everybody, everyone, everything, everywhere, ex, exactly, example, except, far, few, fifth, first, five, followed, following, follows, for, former, formerly, forth, four, from, further, furthermore, get, gets, getting, given, gives, go, goes, going, gone, got, gotten, greetings, had, hadn’t, happens, hardly, has, hasn’t, have, haven’t, having, he, he’s, hello, help, hence, her, here, here’s, hereafter, hereby, herein, hereupon, hers, herself, hi, him, himself, his, hither, hopefully, how, howbeit, however, i’d, i’ll, i’m, i’ve, ie, if, ignored, immediate, in, inasmuch, inc, indeed, indicate, indicated, indicates, inner, insofar, instead, into, inward, is, isn’t, it, it’d, it’ll, it’s, its, itself, just, keep, keeps, kept, know, known, knows, last, lately, later, latter, latterly, least, less, lest, let, let’s, like, liked, likely, little, look, looking, looks, ltd, mainly, many, may, maybe, me, mean, meanwhile, merely, might, more, moreover, most, mostly, much, must, my, myself, name, namely, nd, near, nearly, necessary, need, needs, neither, never, nevertheless, new, next, nine, no, nobody, non, none, noone, nor, normally, not, nothing, novel, now, nowhere, obviously, of, off, often, oh, ok, okay, old, on, once, one, ones, only, onto, or, other, others, otherwise, ought, our, ours, ourselves, out, outside, over, overall, own, particular, particularly, per, perhaps, placed, please, plus, possible, presumably, probably, provides, que, quite, qv, rather, rd, re, really, reasonably, regarding, regardless, regards, relatively, respectively, right, said, same, saw, say, saying, says, second, secondly, see, seeing, seem, seemed, seeming, seems, seen, self, selves, sensible, sent, serious, seriously, seven, several, shall, she, should, shouldn’t, since, six, so, some, somebody, somehow, someone, something, sometime, sometimes, somewhat, somewhere, soon, sorry, specified, specify, specifying, still, sub, such, sup, sure, t’s, take, taken, tell, tends, th, than, thank, thanks, thanx, that, that’s, thats, the, their, theirs, them, themselves, then, thence, there, there’s, thereafter, thereby, therefore, therein, theres, thereupon, these, they, they’d, they’ll, they’re, they’ve, think, third, this, thorough, thoroughly, those, though, three, through, throughout, thru, thus, to, together, too, took, toward, towards, tried, tries, truly, try, trying, twice, two, un, under, unfortunately, unless, unlikely, until, unto, up, upon, us, use, used, useful, uses, using, usually, value, various, very, via, viz, vs, want, wants, was, wasn’t, way, we, we’d, we’ll, we’re, we’ve, welcome, well, went, were, weren’t, what, what’s, whatever, when, whence, whenever, where, where’s, whereafter, whereas, whereby, wherein, whereupon, wherever, whether, which, while, whither, who, who’s, whoever, whole, whom, whose, why, will, willing, wish, with, within, without, won’t, wonder, would, wouldn’t, yes, yet, you, you’d, you’ll, you’re, you’ve, your, yours, yourself, yourselves, zero

Author

I'm a software engineer, an everyday web developer and a maker. I usually build sites with PHP, within or without WordPress. I build Internet of Things with Arduino and ESP8266. I'm the founder of Rockit.it and Dailybest.it and I'm actually the Chief Technical Officer of Better Days web agency.

Recommended

How to remove custom fields from WordPress

Ok. You’ve added custom fields in wordpress, but how can you remove them? How to delete custom fields wordpress Two…

September 29, 2013

13 mysql tips that you don’t know

Here is a list of thirteen tips that can be usefull for your queries. If you know more tips send…

October 19, 2012

PHP function to fix collation on database fields of MySQL

This PHP function search for tables in the active db, match table’s name with the regular expression passed as first…

February 11, 2010

10 PHP usefull functions for MySQLi stuff (mysql improved)

I’ve modified the previous post to use mysqli instead of mysql. Was it necessary? I think it was good step…

January 29, 2010

10 PHP usefull functions for MySQL stuff

Here is my personal collection of 10 php function that I always include in my database function library. I think…

January 25, 2010

MYSQL add counter in a query

Sometimes I’ve needed to add a counter column in a query and I don’t want to add the column with…

January 21, 2010