Weighted search results using MySQL LIKE

Using LIKE, instead of the usual MATCH AGAINST using FULLTEXT.

Here’s the query i’ve made to search events:

SELECT id, title, date_from,
IF (`title` LIKE ‘%$searchTerm%’, 20, IF (`content` LIKE ‘%$searchTerm%’, 10, 0))
+ IF (`content` LIKE ‘%$searchTerm%’, 10, 0)
+ IF (`venue_name` LIKE ‘%$searchTerm%’, 5, 0)
+ IF (`venue_address` LIKE ‘%$searchTerm%’, 5, 0)
AS weight
FROM srpdb.events — used db prefix so phpstorm resolves the sql table for sql dialects
`title` LIKE ‘%$searchTerm%’
OR `content` LIKE ‘%$searchTerm%’
OR `venue_name` LIKE ‘%$searchTerm%’
OR `venue_address` LIKE ‘%$searchTerm%’
ORDER BY `weight` DESC

The MySQL IF() Function

Almost like a ternary in any other language: if(true, doThis, elseDoThis), so we are using it to gives a score to each part that is matched – if the title matches our searchTerm, then assign 20 points etc.

The first IF() in my query has a nested if, again you should read this like a nested ternary (which arent always readable!), so essentially, its doing: If title matches searchTerm, give it 20 points, if no match, then check against ‘content’…

You can then get funky with assigning scores, so here i have given an extra few points if the searchTerm is found in the venue name or address.

Of course, the search results are ordered by weight, giving a fairly accurate set of search results.

Whats that comment about PHP Storm

PHP Storm gets a little confused with my table because its called “events”, there is a table in INFORMATION_SCHEMA also called events, and php storm must bead the first table it finds and tries to resolve column names.

Leave a Reply

Your email address will not be published. Required fields are marked *