Solved

MySQL Fulltext minimum length - changing at runtime?

Posted on 2007-03-24
4
1,236 Views
Last Modified: 2013-12-13
Hi,

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,

Sunil
0
Comment
Question by:SunilDVR
  • 3
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 18784925
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...
0
 
LVL 1

Author Comment

by:SunilDVR
ID: 18784963
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,

Sunil
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18785028
>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.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18785030
you should consider letting the user search by the full-text index (fast), and only refine in a second step using the like method.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question