how can i find best match for a table in mysql db

Question

I have a table in mysql database(product{id, name, category}) which consists of hundreds of records.

Now a new product is added with the same fields and i want to search for the fields and match it with others records so that i could found the best match of product.

My question is what is the best approach for this, should i search through the name of product but what if there are more than one match with that names.

should i use something like this

SELECT * FROM table_name WHERE column1 LIKE '%column1%' OR column2 LIKE '%column2%'

Well, what's the criterium for two products being similar/identical? — deceze, Mar 29 '11 at 06:32
probably you should add keywords-associated with the product. which matches the most of the tags, that should be the best. — S L, Mar 29 '11 at 06:36
@deceze: basically it is my question what criteria should i keep to find best match. — souce code, Mar 29 '11 at 06:38

score 0 · Answer 1 · answered Mar 29 '11 at 06:56

You are looking for the MySQL "MATCH()" function. This comes with a couple caveats:

This requires a fulltext index (meaning you must be using the MyISAM Engine for that table)
Requires MySQL >= 5.1

From the Manual:

When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first.

http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html

score 0 · Answer 2 · edited May 23 '17 at 12:19

0

You should look inty MySQL full-text indexing capabilities for generating relevance-sorted output. There are a number of posts on that subject. See for instance this post.

edited May 23 '17 at 12:19

Community

1
1

answered Mar 29 '11 at 07:00

Thomas

2,231
1
19
27

how can i find best match for a table in mysql db

2 Answers2