Full-text search with ColdFusion using Sphinx
Full text searching is and probably will be for a long time an interesting challenge for any database driven application. Of course ColdFusion already offers a couple of options, though I found most of them somewhat lacking in features or quite complicated to set up.
As we're running mostly on PostgreSQL as database backend, we used to rely solely on the built-in TSearch2 full text search methods of that database. But over the years we have accumulated so much data, some of which is nicely distributed over several tables (the dark side of normalization), that we were really yearning for a less table based and more document focused indexing mechanism - and more speed than TSearch2 could deliver.
Verity never really quite met all of our needs and was a real pain to set up and maintain. CF 9's Solr, which is based on Lucene, might be a mighty step forward, but we're still running on ColdFusion 8, so I really cannot say a lot about handling and performance of the new indexing beast.
For our use cases (i.e. indexing of articles, products in our CMS as well as our forums), Sphinx (for SQL Phrase Index) has shown some amazing results - and we're using it for a couple of months now. In this article I'll show you how to compile, set up and use Sphinx in your ColdFusion application to retrieve search results from documents stored in a PostgreSQL or MySQL database.