At 16:56 14/06/2006, [EMAIL PROTECTED] wrote:
I'm finding that ORDER BY is surprisingly slow, and it makes me wonder if
I'm doing something wrong. Here's the situation:
I need to select a large set of records out of a table, sort them by one
column, and then get just a subset of the sorted list. (For example, I
might want records 40-60 ordered by date, which is a completely different
set than records 40-60 ordered by user ID.) I start with the full list of
record IDs I want, and a query something like this:
SELECT <fields> FROM <table> WHERE recID IN (<record IDs> ORDER BY dateFld
I have a unique index on recID, and an index on dateFld.
Try making another index on both fields at once. SQLite can only use one
index at a time for each query.
So,
CREATE INDEX table_recdate ON table (recID, dateFld);
See if that makes any difference.
When my record IDs list is about 13000 items, the ORDER BY takes about 10
seconds (i.e., the query takes 10 seconds longer than the same query
without the ORDER BY clause). Yet if I remove the ORDER BY, grab all the
dateFld values into my own array, and sort it myself, the sort takes about
2 seconds.
This has left me with the weird result that it's actually *faster* for me
to query the database twice: first to get the unordered list of all
records and their dates, which I then sort myself, and then query again to
get just the subset of records I really want.
(That's what we do in some cases, eg if we have to do some sorts of joins
it's quicker to get all the data and merge it in memory, rather than use
the DB)
Am I missing something here? If my own code can sort these dates in 2
seconds, why does sqlite take 10? And why did indexing the dateFld not
make any difference (i.e., it took about 10 seconds before I added the
index too)?
SQLite hasn't got as powerful an optimiser as some DBs such as MySQL etc
(but then it is a tiny fraction of the size, so what do you expect). Some
DBs also automatically create indices as they decide they're necessary -
SQLite doesn't, you need to do it yourself.
This means you need to think about things a bit more yourself. The
'EXPLAIN' command is your friend - learn how to use it at least a bit if
performance is an issue - you can usually see where there are plain loops
(which go around all records) or indexed loops (which are much quicker).
Usually careful creation of the suitable indices helps a lot.
Read http://www.sqlite.org/optoverview.html - this gives some details of
the limited optimisations that SQLite can do, so you can try to take
advantage of them
Paul VPOP3 - Internet Email Server/Gateway
[EMAIL PROTECTED] http://www.pscs.co.uk/
