MySQL Order By Number of Matches

I'm trying to order a MySQL select statement by the total number of matches in a row. For example, if the table looks like ...

id | dessert | choice
---------------------
1  | pie     | apple pie, chocolate pie 
2  | cake    | chocolate cake, lemon cake, white chocolate cake, triple chocolate cake
3  | donut   | frosted donut, chocolate donut, chocolate cream donut

... and if someone searches for chocolate, the results ought to be ordered:

dessert | matches
-----------------
cake    | 3
donut   | 2
pie     | 1

However, I'm struggling to make the query ordered like this. I've looked at other examples here, but they seem overly complicated for what I guessed would be a fairly simple thing.

Is there a straightforward way to achieve this? I'm new to MySQL, so I'm sorry if this is an obvious question.

Thanks in advance for your thoughts!

P.S. I'm not开发者_运维知识库 able to use fulltext on this table, hence I can't order the results by relevancy.

How about dividing your data into two tables - one defining the desserts and one defining the choices? This would make the queries simpler and faster.

Desserts table:

id | dessert
------------
1  | pie
2  | cake
3  | donut

Choices table:

id | choice
-----------
1  | apple pie
1  | chocolate pie 
2  | chocolate cake
2  | lemon cake
2  | white chocolate cake
2  | triple chocolate cake
3  | frosted donut
3  | chocolate donut
3  | chocolate cream donut

Then you could do something like:

select `dessert`, count(*) as `matches`
       from `desserts` join `choices` using (`id`)
       where `choice` like '%chocolate%'
       group by `id`
       order by `matches` desc

Just strip out the matching word and then compare the difference in string lengths

SELECT id
     , desert
     , ( LENGTH(choice) - LENGTH(REPLACE(choice,'chocolate','')) )
       / LENGTH('chocolate') AS matches
FROM desert_table
WHERE choice LIKE '%chocolate%'
ORDER BY 3 DESC