How can I circumvent MySQL default search character minimum of 4... I need to be able to search for files/pages using only 3 characters.
My original message to Bluehost:
On my ****** account, I host a Concrete5 website (http://www.azselfstorage.org). It is a non-profit self storage association and we are having issues with a search limitation. Specifically, when I search for San Tan Self Storage either on the front end or in the back end, it will not display in the results. If I search for San, Tan, San Tan, or any other rendition it will not show up. My developer offered the following as the root of the issue and the solution:
"I mentioned this earlier to you about contacting bluehost. This is
MySQL's default setting to search which is 4 characters. I looked into alternatives and spoke with some people, but the only realistic
solution is to change MySQL's setting. That said, dropping it down to 3 is a performance hit, so my guess is that bluehost will not be
willing to do this. If I were you, I would send them and email and
Is this in fact the case and how can I go about lowering the default setting for this one website on my hosting package? This causes major issues for us in how we manage accounts. Many of the companies and facilities associated to them have names or initials in names that are 3 letters long. Another example is BPI Capital. If you search BPI... nothing, but if you search Capital, it shows up fine. Please advise..."
"I did check with our admins as to whether this was a setting that could be changed in a shared hosting environment. Unfortunately due to performance restrictions we would be unable to change that setting to a lower value.
You could offload the mysql features you need for the search to a cloud based system like amazon rds (since that type of service charges by the time that the service is active they may be able to enable options like that), or integrate regular expressions to the search that you are performing."
Has anyone else encountered this issue and if so, do you have an easy workaround that is doable in a shared hosting environment? I don't have the option, financially speaking, to pay for a dedicated server.
That being said, you, probably, don't have the proper permissions to do so.
A work around could be to search the database using a regex and using the following syntax:
mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]'; -> 1 mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]'; -> 0
Regardless, MySQL's fulltext search system is fast, but kinda sucks. I do have a class that interprets a natural phrase and converts it to a regular expression, creates an arbitrary rank on each word based on frequency of occurrence in the db, totals those scores for each phrase, then sorts by that score... all in one sql statement. It would be doable to integrate something like that into concrete, but it would take a good amount of work.