A new big game fishing website we did at work, uses the MySQL free text search for the product search.

With tweaking the minimum word length to 3 (ft_min_word_len), creating an index on the name field, and an index on the name, short_description, and long description, of each product (and product options), it was possible to add the scores from these two matches to produce a search (rather crudely) weighted on name. Obviously refinements on the weighting are possible by more matches, and adding some factors for the weighting "a * (MATCH 1) + b * (MATCH 2) + etc, where "a" and "b" are tuned to give the results you want.

alter table products 
 add FULLTEXT(name);
alter table products 
 add FULLTEXT(name,short_description,long_description);

However the MySQL way of matching for scoring means you list the full "MATCH .. AGAINST..." term in bother the "SELECT" and "WHERE" clause, which means that search queries rapidly become very verbose, especially if you start trying to weight term;

SELECT DISTINCT(products.id), products.name, products.thumb, products.short_description , 
(
 MATCH(products.name, products.short_description, products.long_description)
  AGAINST('search term')
 +
 MATCH(products.name) AGAINST ('search term') 
) as scoreproduct 
FROM products, product_options 
WHERE products.visible = 1 
 AND product_options.product_id = products.id 
 AND 
  ( 
   MATCH(products.name, products.short_description, products.long_description)
    AGAINST('search term')
   OR 
   MATCH(product_options.model_id,product_options.note) 
    AGAINST ('search term') 
  )
ORDER BY scoreproduct DESC;

Including the whole "MATCH ... AGAINST ..." repeatedly doesn't seem to fit the whole relational database/SQL model.

Beyond this one can use query expansion in MySQL, but the customer seems content with the results of queries like the one above, and query expansion tends to produce a lot of spurious results. I dare say that Google will do a better job of indexing it within the week, but sites that rely on Google to be their search engine look so terribly amateur, and will miss new products as they are added to the database. I much prefer working with Postgres, but MySQL has these features built in, and the Postgres free text search add-ons feel like a bigger hack, if only because it isn't built in yet.

For static sites we use swish-e, which does a pretty good job. Setting it up for a new site can be a bit of an exercise in love, but it does allow some pretty good refinement in the results. It can also do database driven sites with a bit of love and attention, but again you end up with asynchronous updates, and unless there is significant non-database content that people are searching for, it is more trouble than the MySQL FTS.

Always interested to learn what others are doing here. Seems to me that Google does it so well without any "visible" per site tweaks*, that it must be possible to do it better with "default" settings for most of the sites we do. Perhaps I should just reread the Swish-E manual pages, and play a little more.

I have on the job list, a task that involves doing this in the more general case at some point not too far in the future, as part of building a community based site, that must index sites of community members, turn out custom RSS feeds based on search terms, and maybe even "Google Alert"** style messages etc. Most, but so far not quite all, of the planned functionality is possible with third party tools like Yahoo pipes.

* I know Google do per site tweaks, but I'm guessing based on employees per websites on the Internet, it doesn't happen that often, even if several are doing it as a full time job.

** Who was the science fiction writer who predicted something like these many decades ago? I read the story, I think about an architect whose building falls down or some such vague recollection.