Mysql search two fields and prioritize results with matches on one of the fields

Question

I'd like to write a Joomla search plugin that searches two fields, title and content, for keywords, and orders the results something like title matches with content matches first, title matches only second, and content matches only third. Just getting the title matches ahead of content matches would be sufficient.

I got the following query to work to order titles that match ahead of content that matches, wonder if this is the best thing for the job on function as well as performance/efficiency. Thanks.

select a.id,a.title,a.title REGEXP 'clerk' as amatch, b.content,b.content REGEXP 'clerk' as bmatch from mytable as a left join mytable as b on a.id=b.id where a.title like '%clerk%' OR b.content like '%clerk%' ORDER BY amatch DESC,bmatch DESC

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-01-06T21:21:45

If you want title match with content matches first, I don't think your query does that. Try this variation:

select a.id, a.title, (a.title REGEXP 'clerk') as amatch,
       b.content, (b.content REGEXP 'clerk') as bmatch
from mytable a left join
     mytable b
     on a.id = b.id
where a.title like '%clerk%' OR b.content like '%clerk%'
order by (amatch + bmatch) desc, amatch DESC, bmatch DESC;

Some comments:

You should use the same condition in the select as in the where. It makes it more obvious that these are doing the same thing.
Table aliases a and b refer to the same table. I assume this is an oversimplification, because there would probably be better ways to write the query if it were really on one table.
The addition works in the order by because MySQL treats booleans as integers in a numeric context.

Mysql search two fields and prioritize results with matches on one of the fields

1 Answers