I’ve been looking for a good solution to manage full text search for large data chunks (2GB - 100GB). I’ve written a couple of solutions using Xapian with limited success, but unfortunately I haven’t been satisfied with it overall. Performance was good, but there were a number of issues with flexibility that have me ultimately looking for another solution.

At my usual day job, the topic was brought up and I mentioned Xapian and Lucene as solutions, however we’re looking to stay away from Java as it’s not currently in our architecture, and as I stated before: Xapian doesn’t quite have the capabilities I’m looking for to handle even my own systems. Someone brought up Sphinx as something that was being looked into, and I jumped into the typical research process.

One of the key elements that Sphinx seems to offer …

So, we (dealnews.com) rolled out a new site this month, metaprice.com.  Its young and lacking features of many of the other price comparison sites, but is has great potential.  Our hope is to bring together the best features of all the other players in the market in one great application.
Part of this project required using web services with several different data suppliers.  Most support simple REST and SOAP, but some only offer SOAP.  So, given that I bit the bullet and enabled the SOAP extension for PHP5.  Wow!  I was happily surprised.  The last SOAP code I had looked at was the old PEAR code.  It was not that attractive to me.  It required a lot of work IMO to talk SOAP.

Now, with just 3 lines of code, I can get back a nice object that has all the data I need.  Kudos to Brad …

I came across Sphinx today via the MySQL Performance Blog (which has some good entries you might want to check out). It is an Open Source Full Text SQL Search Engine. It can be installed as a storage engine type on MySQL, and from what I hear can beat the pants off of MySQL's built-in full text search in some cases.

From the web site:

Generally, it's a standalone search engine, meant to provide fast, size-efficient and relevant fulltext search functions to other applications. Sphinx was specially designed to integrate well with SQL databases and scripting …

A

MySQL has supported FULLTEXT indexes since version 3.23.23. VARCHAR and TEXT Columns that have been indexed with FULLTEXT can be used with special SQL statements that perform the full text search in MySQL.

To get started you need to define the FULLTEXT index on some columns. Like other indexes, FULLTEXT indexes can contain multiple columns. Here's how you might add a FULLTEXT index to some table columns:

ALTER TABLE news ADD FULLTEXT(headline, story);

Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements. For example:

SELECT headline, story FROM news
WHERE MATCH (headline,story) AGAINST ('Hurricane');

The result of this query is automatically sorted by relevancy.

MATCH

The MATCH function is used to …