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.

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).

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
—
2012-05-01T02:32:24Z —
#4

busboy said:

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
—
2012-05-01T02:46:52Z —
#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

r937
—
2012-05-01T03:15:56Z —
#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

busboy
—
2012-05-01T03:30:28Z —
#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;

r937
—
2012-05-01T03:35:53Z —
#8

busboy said:

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
—
2012-05-01T05:30:47Z —
#9

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

busboy
—
2012-05-01T20:15:40Z —
#10

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

r937
—
2012-05-01T20:22:43Z —
#11

busboy said:

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

nope, sorry

busboy
—
2012-05-02T13:43:58Z —
#12

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

Mittineague
—
2014-09-23T07:59:34Z —
#13

This topic is now archived. It is frozen and cannot be changed in any way.