MySQL - 按相关性排序的搜索/ SELECT？

Question

I have a table setup as follows:

 userid year  model  color  transmission  interiorcolor wheels
-------------------------------------------------------------------
   1   2005  Fiesta   Red   MT             Black        Stock
   2   2008  Monaro   Red   AT             Black        Stock
   3   2005  F-150S   Blue   AT            Black + Red  Stock
   4   2008  Fiesta   Green   MT           Black        Stock

Now I am building an SQL search (no standard build in full text search), and I want to results to be somewhat relevant.

What i mean is that for example if someone types in "2008 Fiesta", right now with a basic query the results come out in this order

id #1, id #2, id #4

But really I would like #4 to be on the top of the search in that case because it matches my search on two fields instead of just one field.

Thank you!

Answer 1

This is crude but it should work for you:

SELECT id, SUM(Relevance) as SearchRelevance
FROM(
SELECT id, 1 as Relevance
FROM cars
WHERE year = 2008
UNION ALL
SELECT id, 1 as Relevance
FROM cars
WHERE model='Fiesta') AS t
GROUP BY id
ORDER BY SUM(Relevance) DESC

Basically it has a search for each criteria, UNIONs them all together and then groups them and orders by the SUM of relevance (where you get one point per matching item).