[sho]

Oh don't get me wrong, I use CouchDB and love it. I think it's going to be big.

It is very good at what it does. But it is not good at, nor ever will it be good at, ad hoc queries like the GP describes. The second time you query that view it will be blindingly fast. The first time, however, it has to build the view index from scratch, which in a large-ish DB might well take hours.

You just can't assume you will have pretty fast "random access" queries like you would on MySQL or similar. Of course, it goes the other way as well, and there are many examples of views you can trivially do in Couch which would be prohibitively expensive in MySQL.

As always, you pays your money and you makes your choice.

[skorgu]

I know you don't mean it this way but this sounds like SQL databases can magically index their data without scanning it. Add a new index to a large table in any RDBMS and it'll do exactly what you're describing.

Query on an un-indexed column and it'll take forever every time. (There's an Oracle database I deal with occasionally that takes 50 seconds to count 78 rows. No we can't add an index.)

All NoSQL (eewww) non-RDBMSs do is move the pain around. The efficiency and lookup times are (usually, mostly) orthogonal to the orientation of the data, all you can do is align your use case to hit as few pain points as posisble.

[sho]

Oh yeah I know. But RDBMSs tend to be much faster at ad hoc queries.

I don't know what's going on with your Oracle install but even taking the small example of 1k rows, an unindexed ad hoc query in MySQL will return pretty quickly, well under a second on a decent machine/disk. That might be fine for, say, occasional use of a "reports" web page - and you don't need to then store and update an index. The same query on CouchDB will be at least 10 times slower, possibly making the page unusably slow, and if you want it to be usable you need to store the index - no choice.

But yeah, just "moving the pain around" is absolutely right. Ain't no silver bullets.