MySQL Fulltext minimum length - changing at runtime?


I'm using a MySQL database to store an index of articles for a journal. It would be very nice to use a fulltext index on this, however there is a snag: MySQL will only index 4-letter words or greater by default.

Unfortunately, because of the subject, a significant number (perhaps even the majority) of data has keywords which are shorter than this - most commonly 2-3 digits and 3 letters.

I am aware that one can change this limit with the ft_min_word_len configuration option. However, this is a server-wide change which is unfortunately impossible for me to make, as we use a shared webhost (it's a comparatively small project).

I wondered if there's a way of changing the minimum length (say, to 2 characters, which is what we'd need) at runtime. I'm using PHP to query the database from a specific page. I believe, for instance, that one can explicitly view/suppress MySQL errors for individual scripts.

I would like to be able to enable the index for this particular table to include all words of length >=2, rather than the server's default value of 4.

I've looked fairly widely on the web and been unable to find any way of achieving this which doesn't involve a server restart and changing server-wide settings.

Thanks in advance,

Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you cannot change that at runtime, per database level or the like.
the only alternative is to use LIKE instead of full-text search, or create your own "full-text" index using a lookup table...
SunilDVRAuthor Commented:
Hi angellll,

Thanks for your help.

I'm currently using LIKE, though I am a relative beginner at DB work and I've been unable to find a satisfactory solution to searching for multiple keywords (seperated by spaces) against multiple fields (i.e. Title, Description, Keywords), using LIKE on "big ben" will match that phrase only, rather than anything containing "big" plus anything containing "ben". If you could point me in the direction of a reasonably easy way of achieving this type of search over multiple fields it would be appreciated.

With respect to a lookup table - again, as a relative beginner I'm not sure where to start with this. Any guidance/links to relevant tutorials would be very much appreciated.

Thanks very much,

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>using LIKE on "big ben" will match that phrase only

you could search for LIKE '%big%ben%' or like LIKE '%ben%big%', but with 3 words this would mean already 6 combinations to search for etc...

full text would indeed by fine, but does not allow by default shorter words than 4 characters... and is server-wide setting.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should consider letting the user search by the full-text index (fast), and only refine in a second step using the like method.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.