Comparison of full text search engine – Lucene, Sphinx, Postgresql, MySQL? [closed]

by

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 no idea how applicable to your situation this is, but Evan Weaver compared a few of the common Rails search options (Sphinx, Ferret (a port of Lucene for Ruby) and Solr), running some benchmarks. Could be useful, I guess.

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.

More Related Contents:

  1. Choosing a stand-alone full-text search server: Sphinx or SOLR? [closed]
  2. How to filter SQL results in a has-many-through relation
  3. How to select the nth row in a SQL database table?
  4. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  5. Search for “whole word match” in MySQL
  6. Simulating MySQL’s ORDER BY FIELD() in Postgresql
  7. MySQL match() against() – order by relevance and column?
  8. Simple Random Samples from a Sql database
  9. SQL: Repeat a result row multiple times, and number the rows
  10. mysql fulltext search failure
  11. Why do results from a SQL query not come back in the order I expect?
  12. Best practices for SQL varchar column length [closed]
  13. Relationship between catalog, schema, user, and database instance
  14. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  15. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  16. What resources exist for Database performance-tuning? [closed]
  17. How to evaluate hosted full text search solutions?
  18. Django MySQL full text search
  19. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  20. Are SELECT type queries the only type that can be nested?
  21. how to cast the hexadecimal to varchar(datetime)?
  22. Postgres LEFT JOIN with WHERE condition
  23. How do you write a case insensitive query for both MySQL and Postgres?
  24. Foreign key to one of many tables?
  25. Import MySQL dump to PostgreSQL database
  26. MySQL Fulltext Search Score Explained
  27. What are the max number of allowable parameters per database provider type?
  28. What happens when I exhaust a bigint generated key? How to handle it?
  29. How to allow fulltext searching with hyphens in the search query
  30. Which DB technology is sued to solve the following search issue? [closed]

Leave a Comment