Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL Fulltext minimum length - changing at runtime?

Posted on 2007-03-24
4
Medium Priority
?
1,250 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 1000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

688 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