Not being able to search for three letter words

Databases
1

I get complaints from my users that they can’t search on three letter words like “eye”. For years I was under the assumption that it’s impossible with mySQL. Today I decided that I better ask the experts to see if I’m wrong. Here is the current query that I have in place. Please advise.

Thanks!

SELECT tID, date_format(date, ‘%m-%d-%Y’) as date, viewed, summary, MATCH (summary,testimony,keywords) AGAINST (‘$q’) AS score FROM testimonies WHERE MATCH (summary,testimony,keywords) AGAINST (‘$q’) and approved = ‘Yes’ order by score desc limit 50

2

It certainly is possible with MySQL. There is an option ft_min_word_length you can put in the [mysqld] section of your my.ini and set it to the minimum length you want the full text search to be able handle (so in your case ft_min_word_length = 3).

You can read all about it in da manual over here: http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html

3

Ok, it looks like these types of changes require a restart of the server. I pay a monthly hosting fee and so I don’t have physical access to reboot the server on my own. What does someone in my situation do?

Thank you!

4

use LIKE instead of MATCH

not quite as fast, but unless you have millions of testimonies, the difference shouldn’t be noticeable

5

Rudy, do I need to have my ISP upgrade the mySQL version? Here is my query:

SELECT tID, date_format(date, ‘%m-%d-%Y’) as date, viewed, summary, LIKE (summary,testimony,keywords)
AGAINST (‘eye’) AS score FROM testimonies WHERE LIKE (summary,testimony,keywords) AGAINST (‘eye’)
and approved = ‘Yes’ order by score desc limit 50;

Response:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘upgrade’ at line 1

Action:

upgrade

6

the error message doesn’t match the query you posted, as it doesn’t contain the word “upgrade”

LIKE has different syntax from MATCH, it’s effectively a rewrite

7

Oh, you’re right. Here is the real one.

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘LIKE’ at line 1

I am familiar with using like by itself, but maybe in a query like this, where multiple columns are being examined, I have the format wrong:

SELECT tID, date_format(date, ‘%m-%d-%Y’) as date, viewed, summary, LIKE (summary,testimony,keywords)
AGAINST (‘eye’) AS score FROM testimonies WHERE LIKE (summary,testimony,keywords) AGAINST (‘eye’)
and approved = ‘Yes’ order by score desc limit 50;

8

there is only one way to use LIKE

SELECT tID
     , DATE_FORMAT(date,'%m-%d-%Y') as date
     , viewed
     , summary
     , CASE WHEN summary   LIKE '%eye%' THEN 1 ELSE 0 END +
       CASE WHEN testimony LIKE '%eye%' THEN 1 ELSE 0 END +
       CASE WHEN keywords  LIKE '%eye%' THEN 1 ELSE 0 END AS score
  FROM testimonies 
 WHERE approved = 'Yes'
   AND ( summary   LIKE '%eye%'
      OR testimony LIKE '%eye%'
      OR keywords  LIKE '%eye%' )
ORDER 
    BY score DESC LIMIT 50;

you could run this whenever the user’s search term is a single word of less than 4 cfharacters, and continue to use the MATCH query for other search terms

9

They don’t mean to restart the physical server, just the mysqld daemon. If you have root access you can do that :slight_smile:

10

That did it. Thanks again Rudy. Are you on elance.com by the way?

11

nope, sorry

12

Maybe you should be. It would be an excellent way for you to make extra money with your powerful database skills.