MySQL - weighting fields in LIKE searches

Posted 13:32, 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.

Please login to post a comment.

Search this site
Login
(or login/signup the old fashioned way)
Elsewhere

External URLs/articles that may be of interest:

ZF Blog Application tutorial

This is the first part in a multi-part tutorial in creating a blogging app using ZF. It serves as a great introduction for people trying to get started with the framework, and looks at application structure, mvc, templating and ACL.

Drupal in the Enterprise

An interesting look at why the author doesn't feel Drupal is ready for the enterprise. Drupal certainly appears to be one of the better CMSes around, so some of these criticisms seem a little harsh when you compare it to some of its inferior competitors. However many can also be applied to PHP web apps in general.

BBC admits Linux usage figures were off

Recently the BBC said one of the reasons they didn't push for a Linux compatible version of their iPlayer was that their website only received about 600 visits a week from Linux users. They've now admitted this figure is closer to 100,000. Just a bit off then.

Online ad tracking 'opt out' list

Lobbyists in the US are trying to get an 'opt out' list created for online advertising, so users could choose not to have their browsing habits tracked. The irony is, for this to work technically, some system somewhere would have to create a huge list of IP addresses and personal information, which seems to defeat the point.