MySQL - weighting fields in LIKE searches

Posted 13:32, on 22/4/2008 in Web

A client asked us today if we could do some work on their search feature to weight the ordering of results if the search term appeared in the title. The site is running a fairly old version of one of our products which uses a very basic LIKE search (the current version of the same system uses a Lucene-based system). The example search term they gave was a 3-letter word, which pretty much rules out MySQL fulltext (when you start reducing ft_min_word_len fulltext searching gets pretty slow), so we came up with a way of weighting standard LIKE searches:

SELECT title, description,
IF(title LIKE '%who%', 3, 0) +
IF(description LIKE '%who%', 2, 0) AS weight
FROM `products`
WHERE fullname LIKE '%who%' OR brief_description LIKE '%who%'
ORDER BY weight DESC

What this does is create an arbitary 'weight' value purely based on which field(s) the search term appears in. This value is used to order the products.

Yes, it's slow, as it's doing a two LIKE searches for every field, for every search term. But in this relatively small dataset (~200 products), it still runs in a fraction of a second so it's okay as a temporary solution.

Share:

Comments (0) Tags: search, mysql