Solved

MySQL Fulltext minimum length - changing at runtime?

Posted on 2007-03-24
4
1,241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 143

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 143

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 143

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

756 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