Rev cgi & mySQL (again)
jbv.silences at club-internet.fr
Sat Jan 3 14:14:31 CST 2009
For those interested in the latest episodes of my struggle with mySQL,
here's a summary : I've tried the MATCH (col1) AGAINST ("word1 word2")
construct, and it's blazing fast (roughly 50 to 100 times faster than my previous
queries full of OR statements...
But there's a couple of drawbacks : columns on which MATCH.. AGAINST is
used need to have a fulltext index attached to them (which is logical), BUT there's
a minimum word length limit on such indexes (default length is 4, set with a mySQL
global). No wonder it's so fast, since lots of words are ignored...
So, if you need smaller words to become searchable, you need to modify the
ft_min_word_len global (setting it to 1 if you need all words to be searchable) and
restart mySQL... and reconstruct your indexes as well.
And then searches via MATCH ... AGAINST become almost as slow as regular
Oh, and I forgot to mention that there's also a list of rejected words that can be modified
in another global...
More information about the use-livecode