Comparison of full text search engine - Lucene, Sphinx, Postgresql, MySQL?

I'm building a site and I am looking for a search engine.

A few candidates:

• Lucene/Lucene with Compass/Solr
• Sphinx
• Postgresql built-in full text search
• MySQl built-in full text search

Selection criteria:

• result relevance and ranking
• searching and indexing speed
• ease of use and ease of integration with Django
• resource requirements - site will be hosted on a VPS, so ideally the search engine wouldn't require a lot of RAM and CPU
• scalability
• extra features such as "did you mean?", related searches, etc

Anyone who has had experience with the search engines above, or other engines not in the list -- I would love to hear your opinions.

EDIT: As for indexing needs, as users keep entering data into the site, those data would need to be indexed continuously. It doesn't have to be real time, but ideally new data would show up in index with no more than 15 - 30 minutes delay.

 Answers

Good to see someone's chimed in about Lucene - because I've no idea about that.

Sphinx, on the other hand, I know quite well, so let's see if I can be of some help.

• Result relevance ranking is the default. You can set up your own sorting should you wish, and give specific fields higher weightings.
• Indexing speed is super-fast, because it talks directly to the database. Any slowness will come from complex SQL queries and un-indexed foreign keys and other such problems. I've never noticed any slowness in searching either.
• I'm a Rails guy, so I've no idea how easy it is to implement with Django. There is a Python API that comes with the Sphinx source though.
• The search service daemon (searchd) is pretty low on memory usage - and you can set limits on how much memory the indexer process uses too.
• Scalability is where my knowledge is more sketchy - but it's easy enough to copy index files to multiple machines and run several searchd daemons. The general impression I get from others though is that it's pretty damn good under high load, so scaling it out across multiple machines isn't something that needs to be dealt with.
• There's no support for 'did-you-mean', etc - although these can be done with other tools easily enough. Sphinx does stem words though using dictionaries, so 'driving' and 'drive' (for example) would be considered the same in searches.
• Sphinx doesn't allow partial index updates for field data though. The common approach to this is to maintain a delta index with all the recent changes, and re-index this after every change (and those new results appear within a second or two). Because of the small amount of data, this can take a matter of seconds. You will still need to re-index the main dataset regularly though (although how regularly depends on the volatility of your data - every day? every hour?). The fast indexing speeds keep this all pretty painless though.

I've not plumbed the depths of MySQL's full-text search, but I know it doesn't compete speed-wise nor feature-wise with Sphinx, Lucene or Solr.

---

I am surprised that there isn't more information posted about Solr. Solr is quite similar to Sphinx but has more advanced features (AFAIK as I haven't used Sphinx -- only read about it).

The answer at the link below details a few things about Sphinx which also applies to Solr. 

Solr also provides the following additional features:

1. Supports replication
2. Multiple cores (think of these as separate databases with their own configuration and own indexes)
3. Boolean searches
4. Highlighting of keywords (fairly easy to do in application code if you have regex-fu; however, why not let a specialized tool do a better job for you)
5. Update index via XML or delimited file
6. Communicate with the search server via HTTP (it can even return Json, Native PHP/Ruby/Python)
7. PDF, Word document indexing
8. Dynamic fields
9. Facets
10. Aggregate fields
11. Stop words, synonyms, etc.
12. More Like this...
13. Index directly from the database with custom queries
14. Auto-suggest
15. Cache Autowarming
16. Fast indexing (compare to MySQL full-text search indexing times) -- Lucene uses a binary inverted index format.
17. Boosting (custom rules for increasing relevance of a particular keyword or phrase, etc.)
18. Fielded searches (if a search user knows the field he/she wants to search, they narrow down their search by typing the field, then the value, and ONLY that field is searched rather than everything -- much better user experience)

BTW, there are tons more features; however, I've listed just the features that I have actually used in production. BTW, out of the box, MySQL supports #1, #3, and #11 (limited) on the list above. For the features you are looking for, a relational database isn't going to cut it. I'd eliminate those straight away.

Also, another benefit is that Solr (well, Lucene actually) is a document database (e.g. NoSQL) so many of the benefits of any other document database can be realized with Solr. In other words, you can use it for more than just search (i.e. Performance). Get creative with it :)

---

I'm looking at PostgreSQL full-text search right now, and it has all the right features of a modern search engine, really good extended character and multilingual support, nice tight integration with text fields in the database.

But it doesn't have user-friendly search operators like + or AND (uses & | !) and I'm not thrilled with how it works on their documentation site. While it has bolding of match terms in the results snippets, the default algorithm for which match terms is not great. Also, if you want to index rtf, PDF, MS Office, you have to find and integrate a file format converter.

OTOH, it's way better than the MySQL text search, which doesn't even index words of three letters or fewer. It's the default for the MediaWiki search, and I really think it's no good for end-users.

In all cases I've seen, Lucene/Solr and Sphinx are really great. They're solid code and have evolved with significant improvements in usability, so the tools are all there to make search that satisfies almost everyone.

for SHAILI - SOLR includes the Lucene search code library and has the components to be a nice stand-alone search engine.

---

SearchTools-Avi said "MySQL text search, which doesn't even index words of three letters or fewer."

FYIs, The MySQL fulltext min word length is adjustable since at least MySQL 5.0. Google 'mysql fulltext min length' for simple instructions.

That said, MySQL fulltext has limitations: for one, it gets slow to update once you reach a million records or so, ...