Jump to content

like or match for phrase


Recommended Posts

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?






Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.