mysql query

MrLister

I have a lyrics table in the database however I'm having some problems with it

this is the query

select * from lyrics where match(artist,title) against('system') and match(artist,title) against('down')

This return 0 results. But I know there are 42 rows that contain the sentence "system of a down" in that table. if i do

select * from lyrics where match(artist,title) against('system')

Then I will get all the system works but the other one doesn't work... Why?

Help is greatly appriciated.

Thanks

MrLister

anyone?

xblue

okay, I'll give it a try...

have you thought about stopwords? if 'down' should be contained in more than 50% of your records it would be one, and the way you put your query this would make it fail.

Stopwords would be normally ignored, but since you split your where clause into 2 parts, the latter
match(artist,title) against('down')
would match 0 records - I've just checked on that - and since you connect them with AND you can't get any result at all.

There is also a user contributed note on mysql saying

I just discovered that MATCH (name,description) AGAINST ('$search') doesn't return any results if $search < 6 chars

but I'm not sure about this.

Hope this helps,
xblue

diego25

I've read the MySQL documentation when I read his question (but couldn't find an answer, that's why I didn't reply 😛 ), and it says that words with less than 3 characters (not 6) will not be used in the search because they are too short.

Diego

MrLister

I finally found the solution. I didn't know that in against('word1 word2 word3') don't have to be exact instead it will search for all those words seperatly in thsoe fields and return any results. so what i did now is

match(artist,title) against('system down') and it does show system of a down