Solved

MySQL Fulltext minimum length - changing at runtime?

Posted on 2007-03-24
4
1,235 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
only allow numbers with preg match 4 27
Update from TABLE-A to TABLE-B 5 39
How to set a countdown by not using the PC time in PHP 36 45
mysql update statement 3 0
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
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 …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

862 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now