[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[GNUnet-developers] Re: dropping support for SQlite from GNUnet due to b
D. Richard Hipp
[GNUnet-developers] Re: dropping support for SQlite from GNUnet due to bug? workaround?
Sun, 21 Aug 2005 07:14:34 -0400
On Sun, 2005-08-21 at 00:04 -0700, Christian Grothoff wrote:
> SELECT * FROM gn070 WHERE condition ORDER BY criteria LIMIT 1;
> Note that LIMIT 1 pretty much ensures that the result size is less than 64k
> data. What does SQlite do? As far as I can tell, it loads the ENTIRE
> database into memory (using 1 GB+ of memory).
This is a misunderstanding on two counts. First off, the LIMIT
clause applies after the ORDER BY has run. So the ORDER BY has
to sort the entire results set of the query first, then the LIMIT
clause says to use only the first row of the sorted results set.
That's the way all SQL engines works. So if the result set of
the query without the LIMIT clause is big, then a lot of sorting
is going to have to happen even if you only look at the first
row of the result.
Many (but not all) SQL engines sort on disk. SQLite and a few
others sort in memory. Sorting in memory is faster, but as you
observe, it breaks down when you get a really big result set.
This is a tradeoff. Note, however, the SQLite is not loading
the entire table into memory, only that part which must be sorted.
I guess in your case that must be a pretty large subset of the
table. Another database engine that sorts on disk might not
blow out memory, but it will take a really, really long time to
finish your query. So either way, you have a problem.
The solution here is to choose a good index so that the ORDER BY
clause can be optimized out and no sorting needs to occur at all.
If you query is like this:
SELECT * FROM tbl WHERE a=EXPR AND b=EXPR ORDER BY c, d LIMIT 1
Then use this index:
CREATE INDEX idx ON table(a,b,c,d);
In other words, add the terms you are sorting by to your index
after the equality constraints terms from your WHERE clause.
With such an index, SQLite (and most other SQL database engines)
will reach right in and pull out the single row you are interested
in. No sorting will occur and the query will finish in milliseconds.
D. Richard Hipp <address@hidden>