The MySQL fulltext search feature allows you to index your database and make the text fully searchable. If you want to index character counts less than 4, you will need to adjust your MySQL configuration.
This is more a simple share than a tutorial. Back in 2004 I was working on a site that had a relatively small database (MYSQL), with about 50K records.
The DB was indexed so that full text searching could be used, but I found that whenever a user would search for common words under a certain length (in this case under 4 letters), no results would be returned. An example of a query that I tested was “ASP”, which of course returned zero results.
This is simply because by default, MYSQL fulltext is setup to allow only indexing of 4 letters. I’ve never had to change my maximum word length, but according to the MYSQL documentation – the length varies by version. Anyone have more insight on this?
To change your minimum word length, you must simply edit the ft_min_word_length by adding it to an options file like this :
[mysqld]
ft_min_word_len=3
Once you have done this – you must rebuild your indexes, or like me, not read the documentation and wonder why it isn’t working for 2 hours. In addition to changing the word length, you may have to over ride the installed default stopword list. You can find more information on how to do this at the MYSQL Fulltext doc page.
I haven’t had a need to use fulltext searching since that project in 2004, but I thought I would save someone out there a couple of hours of hair pulling. You will also notice on the MYSQL documentation pages, information on search thresholds. I didn’t find the need to tweak these parameters, but for fun times sake – I will have to do so in the near future and share my results. Most people find that the default stoplist and thresholds meet their needs, but I can imagine a few scenarios where you would want the fulltext features of MYSQL to work quite different.
For example – an acronym database. What a project to tackle – getting the search threshold and stop list to NOT return every result for AAA, and also to NOT return zero sets.
Interesting.
Leave Your Response