[100k]

Great post with obvious real world experience behind it.

I think it's a fantastic point that you should focus on optimizing your database before you start adding caching. If you can tune your DB with the right indexes and give it enough RAM to fit the whole dataset, you've got a great cache right there!

(BTW, I have been using PostgreSQL on my latest project. I'm impressed so far. It has a much query optimizer and better indexes than MySQL.)

I also like using Solr/acts_as_solr. I haven't used Sphinx but from what I've read about setting it up it sounds incredibly fiddly. Solr, by contrast, is quite simple.

[wmoxam]

Have recently switched from using Solr+acts_as_solr to Sphinx+Thinkinh Sphinx I have found quite the opposite. Thinking Sphinx is simpler than Solr and required no 'fiddling'.

I had to work around some problems in acts_as_solr, such as it's tendency to automatically update solr as soon as a DB record changed (regardless if no fields that solr is indexing have changed). Thinking Sphinx simply updates it's index in a batch process that is called by cron (and is super fast!). I highly recommend it.

[danielrhodes]

Sphinx isn't hard to set up, but (from what I've heard) Solr is a much better choice if you want very fine tuning. Sphinx is a great out of the box solution and will fit most people's needs quite well.