Not being able to search for three letter words

[busboy]

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

[ScallioXTX]

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/...ne-tuning.html

[busboy]

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!

[r937]

What does someone in my situation do?

use LIKE instead of MATCH

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

[busboy]

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

[r937]

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

[busboy]

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;

[r937]

I am familiar with using like by itself...

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

[ScallioXTX]

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

[busboy]

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

[r937]

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

nope, sorry

[busboy]

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