MySQL Ordering Results by the order of Where Clause Matched

Question

I have a query that tokenizes a phrase on spaces and needs to look for the best match for the phrase. It needs to return that match first. I'm trying to do this in a single query using where clauses. Is this possible? I'm trying something along these lines:

for: my phrase

select name from myTable where name="my phrase" or name like "%my phrase%" or (name like "%my%" and name like "%phrase%") or (name like "%my%" or name like "%phrase%") limit 5;

Basically I want the top five matches in order of where clause matched. I know that by default MySQL evaluates the where clauses in an optimized manner. Is there any way to force it to evaluate them in order? And if so, how can I make it return the results in order of where clause matched?

Answer 1

Use Fulltext search instead of this. It's much faster, more flexible and let you score results. Google for more

SELECT ..., MATCH(col_name) AGAINST ('my pharse') AS score FROM tbl_name WHERE MATCH(col_name) AGAINST('my pharse') ORDER BY score DESC;