gnunet-developers
[Top][All Lists]
Advanced

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[GNUnet-developers] Re: dropping support for SQlite from GNUnet due to b


From: Christian Grothoff
Subject: [GNUnet-developers] Re: dropping support for SQlite from GNUnet due to bug? workaround?
Date: Sun, 21 Aug 2005 14:21:16 -0700
User-agent: KMail/1.7.2

First of all, thanks for the quick response.  You suggested adding an index, 
however we do have an index on all of the colums.  Als Nils pointed out in a 
private E-mail to me, the index only works for equality constraints, as in 
your suggestion:

>    SELECT * FROM tbl WHERE a=EXPR AND b=EXPR ORDER BY c, d LIMIT 1

However, our query looks like this:

SELECT * FROM tbl 
   WHERE (e > :4 AND p == :5) OR p > :6 
   ORDER BY p ASC e ASC h ASC 
   LIMIT 1

The index is created with:

CREATE INDEX idx_h ON tbl (h)
CREATE INDEX idx_p ON tbl (p)
CREATE INDEX idx_e ON tbl (e)

Maybe something is wrong with the way we create the index? (should we create 
one "big" index on h,p,e instead?) Note that the original WHERE clause may 
indeed select the entire DB (!), however after order by p and e there should 
already only be a relatively small subset left.

What needs to be done to make SQlite use the index instead of sorting here?  
That would likely solve the problem.

Happy hacking

Christian

On Sunday 21 August 2005 04:14, D. Richard Hipp wrote:
> 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 of 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.




reply via email to

[Prev in Thread] Current Thread [Next in Thread]