MySQL Order By Number of Matches

Question

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.

Answer 1

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