Recently I've found myself chafing at the limitations of document indexing engines. I was developing a small website that needed some fairly robust searching capabilities but due to their hardware constraints I couldn't deploy a Lucene-ish solution (such as Solr or ElasticSearch, like I normally would) to handle this need.
And even then, while I needed to serve up some complex data and calculations that were database-intensive, I didn't need to handle more than 250k potential records. Deploying an entire Solr or ES instance just to handle this seemed like a waste.
After I thought about it, it seems like a fairly large problem. Most people handle search requirements solely with SQL. They just run SQL queries for their data and that's that. Their search capabilities also end up being terrible.
Doing a blanket full-text wildcard search can be painfully slow on some systems (shared hosts in particular) and bog down your database, especially if you have complicated queries and lots of joins.
You end up doing multiple queries on a single request from the user. You might get around this with ever-more-complicated queries, but see the previous point.
Lack of features typically present in full-text engines.
Databases had the same problem of needing to be deployed as a server and then SQLite came along and suddenly we could deploy a database that is self-contained in a single file. My Googling has produced nothing - wonder if something exist like this for full-text indexing/searching.
What factors to take into account when deciding whether to implement lightweight document indexing (eg as explained in answers to another question) or keep using SQL for these situations?