Topic awaiting preservation: MySQL: Boolean MATCH problems...

Ok... I'll say now that I understand how MATCH works in MySQL (that includes that MATCH doesn't accept 3 letter words by default), I've done my share of research on it. But my problem is that it wont match some words. I try the words not working with a LIKE and get the results. I use MATCH because it's not only faster but easier (in boolean mode) IMO.

I have records in the table with 'orange' and 'wish' within their names. When using the MATCH statement, only 'orange' returns any results while 'wish' returns nothing. Not the same story with LIKE.

Example queries:

SELECT * FROM `mytable`
 WHERE MATCH(`mytable`.`thingname`) AGAINST('+"orange"' IN BOOLEAN MODE)

SELECT * FROM `mytable`
 WHERE `mytable`.`thingname` LIKE '%orange%'

Does anyone have an explanation why MySQL's MATCH is doing this, or more importantly, how can I get around it?

probably because of the '50%-rule'.
Words must occur in less than 50% of your documents to actually be found
(That's basically an implicit stop word filter).
Now, if you only have a limited amount of documents, it's quite possible for 'wish' to occur in about half of them ;-).

so long
->Tyberius Prime

PS: Save the Ozone (look at the top of the page ;-) )

• 10 of my 2653 records have the word 'wish'
• 59 of my 2653 records have the word 'orange'

So now what?

ok...
what's with the additional quotes around orange? what happends if it reads
AGAINST ("orange" ...) - do you have a mysql version that supports +?
What happens if you drop the boolean mode?
Is orange in your text really a work. Do you get results when you do like '% orange %' (note the spaces - and I now this doesn't cover all cases).

Why would that be an issue if 'orange' works perfectly fine while 'wish' doesn't?

First why are you using boolean mode with no -something? That just slows the search down.

Second what's your default minumum word value set at? Default is 3 but someone may have set this higher for performance reasons.

Finally I think TP is right AFAIK putting the quotes around the "orange" should be looking for a literal "orange" instead of orange.



.:[ Never resist a perfect moment ]:.

I tried a bunch of combinations... nothing. I tried with and without boolean mode, with and without quotes. This just has me stumpped. I'm sure my minimum word value is at the default 3 because all other 4 letter words work ok with no problem.