like or match for phrase

[ayoksus]

I have some questions about match phrase (more than 1 word) with phrase. I have some items with keywords which I need to match.

Case 1: the keyword is "lady's-shoes" and I need to get items with keywords "lady's-shoes lady's-shoe woman's-shoe" but not to match "lady's-jeans lady's-pants".

Case 2: the keyword is "long dress green" and I need to get items with keywords "long dress" and "long dress green" and "long dress green for woman" but not "green shoe " nor "long dress red"

 

Which one do I need to use, LIKE or MATCH AGAINST? Do I need '%%' for the LIKE? 

 

Case 3: case 1 without dash (-). Is it better to find if I use dash between words?

 

Thank's.

 

Rgds,

ayok

[ragax]

It depends on how much data you have: there's a cost-benefit balance to maintaining a fulltext index.

 

If you know exactly what you want to match and not match, and you don't have thousands of rows, then LIKE or (REGEXP if you have complex but precise match conditions may perform best---you'd have to test it. Understand that with those, searching for text within text, you'll have to read the entire field, the optimizer cannot use an index. For exact matching, the other option is a fulltext MATCH in boolean mode. If you have a lot of rows, that's probably going to perform better than reading every single row.

 

If you want to "fuzzy match," then  you're looking at a fulltext MATCH. In the default mode it ranks the matches, and you have to choose a threshold.

 

Do you have MySQL 5.6? InnoDB now has full text search. I have no experience with it.

 

My initial thoughts, and not a full answer: someone more experienced than me will probably have better information for you.