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. -- D. Richard Hipp <[EMAIL PROTECTED]> _______________________________________________ GNUnet-developers mailing list [EMAIL PROTECTED] http://lists.gnu.org/mailman/listinfo/gnunet-developers
