Of course, "large" is not about the data necessarily, but about the analysis of the data. It sounds like you presented sqlite with a query which would need more than the resources of your computer in order to execute as you specified. This can be really easy to do accidentally on any DB engine with something like a cartesian product.

It is true that sqlite doesn't have as good of a query plan optimizer as larger RDBMSes, and is a little lower-level, but the tradeoff of having the simplicity is that you must understand a little more of the internals to design more complex queries.

How you considered you might have used it wrong? If everyone else says it works great and you use it and have an issue my first thought would be I must have done something wrong not this sucks everyone else must be wrong.

Sound logic, but I didn't think to myself "gee everyone else is wrong". I just noticed SQLite did something I've never seen another database do and figured it's not made for this.

I find this happens quite often - once you stray from the most common path for $AWESOME_TOOL, you start finding little oddities here and there. It's not that 'everyone else is wrong', it's just that you're off the beaten track.

I'm sure if you posted the EXPLAIN here, someone might be able to figure out why it did that. Were you able to test the exact same query on a different database?

I was going to, but something else got in the way and I ended up not running the query again.

Over the years I've had my share of complex queries with subqueries and aggregates, both for class and for my own projects, but never have I encountered 70MB exploding into multiple gigabytes (I don't even know what its final size would have been). I guess I could have used EXPLAIN and dug into it, but never having had this before I figured it was SQLite not being made for it.

Perhaps this is a bug. If you post the query and database schema on the sqlite mailing list or here we can take a look easily enough. If you can upload the database somewhere we can try to reproduce it.

Mailing list: http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

That seems odd. Have you looked at the SQL docs for why this might be?

Also, have you compared this to what other DBs do?

I've used SQLite with a 5GB database and tables up to about 15m rows, and while I've certainly managed to chew up a lot of disk (same as SQL Server or Postgres), it's not the day to day experience, even with quite complex queries. All you can do is EXPLAIN QUERY PLAN, or perhaps look into putting temp files in RAM, if that is more plentiful:

http://stackoverflow.com/a/19259699/24618

Just last week I played with a project that involved inserting 40M words (and their tuples, triplets, quadruples, ...) into in memory SQLite database. Even though the db was about 600MB a query which grouped the tuples by their frequency finished in under 5s.

FWIW MySQL performs in exactly the same way, any temporary tables by default go to /tmp

this includes whatever you stick in parenthesis.

`select * from table where account_id in (select id from accounts where name like 'Fred%');` for example

For analytics, monetDB is my new small local db.

MonetDB lite embeds in R like rsqlite.

Whatever your issue was, it most likely has to do with our query planning or lack of indexes. I've used sqlite with success on many gigabyte datasets.

If you're like most other developers, your tables probably look like Excel sheets. No wonder it performs badly.