lucene-solr-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Fuad Efendi" <>
Subject RE: SOLR vs SQL
Date Wed, 02 Sep 2009 16:27:33 GMT
Hi gwk,

Thanks for reply!

Yes, SOLR gives out-of-the-box
- indexes
- implicit data normalization
- fault-tolerance, replication, scalability
- performance

(so that we can save _huge_ money & time)

But from just an engineering viewpoint, forgetting cost&time, SELECT
COUNT(*) ... WHERE ... seems to be faster than SOLR faceting on generic
queries like id:[* TO *] (I believe SOLR still counts terms / intersects
DocSets 'from scratch' even for queries returning _all_ documents in index,
and even for filtered by specific term value...)

For queries returning unpredictable subset of documents ('full-text', where
regular database index can't be used) SOLR faceting is the only solution
(database can't do that...)

Of course, development time and reduced cost is very important...

And, agree, if we have normalized schema with 20 parent 'attributes' and
single child 'entity' writing dynamic filter in SQL on 0 to 20 attribute
values (and maintaining 40 indexes) may take some costly effort and

> Hi Fuad,
> The search box is only used for geographical search, i.e.
> country/region/city searches. The watermark on the homepage indicates
> this but the "search again" box on the search results page does not,
> I'll see if we can fix that.
> We use Solr not so much for the searchbox, which to be honest was an
> afterthought. But we do use Solr for faceting. Honestly, the thought of
> writing an SQL query which calculates all these facet counts every time
> a search parameter is changes gives me a headache, I don't think it's
> possible to do it in one query (although maybe, but I don't think
> anybody would want to maintain it). As for performance, every nontrivial
> database/search engine is affected by dataset for all but the simplest
> queries, and in my tests Solr trumps Mysql by a huge margin for our use
> case. We use a database to store our data in a somewhat normalized way,
> which is good for data consistency, but not so good for retrieval
> speeds. This is what makes Solr so useful for us, we can index all data
> in denormalized form with all data for a property in one record. While
> the (sql) database remains authoritative
> Full-text search is only one part of Solr, while an important part it
> isn't the only reason for using Solr. In our case, since we provide
> support for multiple language we try not to store textual descriptions
> but every facet a property can have. This gives us exactly the data
> needed to perform faceting but not so much on the full text search
> (which is used mind you, to find suggestions when you use the search box).
> Regards,
> gwk

View raw message