mysql: order by match

Question

for looking up matching keywords in mysql i use

SELECT * FROM `test` WHERE `keywords` REGEXP '.*(word1|word2|word3).*' LIMIT 1

I want to order them by the most matching keywords in the keywords column to give the best answer.For example

Keywords /////////////// Response
word1,word2 /////////// test1
word1,word2,word3 / test2

I want the response to be test2 with the query given.
How can i order the results my the most matching keywords?

Is the column `keywords` a comma separated list or free text? — ypercubeᵀᴹ, Dec 04 '11 at 20:41
**Is storing a comma separated list in a database column really that bad?:** http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad — ypercubeᵀᴹ, Dec 04 '11 at 20:55

score 2 · Accepted Answer · answered Dec 08 '11 at 13:15

SELECT 
  (keywords REGEXP '.*(word1).*')
  +(keywords REGEXP '.*(word2).*')
  +(keywords REGEXP '.*(word3).*') as number_of_matches
  ,keywords
  ,field1
  ,field2
FROM test 
WHERE keywords REGEXP '.*(word1|word2|word3).*'
ORDER BY number_of_matches DESC 
LIMIT 20 OFFSET 0

mysql: order by match

1 Answers1