Have you tried to create an indexed?
Have you tried to analyze your query with SQLiteManager in order to
see which indexes are used?
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
On Jun 14, 2006, at 5:56 PM, [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.
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.
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)?
Finally, can anyone see a more efficient solution to this problem?
Many thanks,
- Joe
--
Joe Strout -- [EMAIL PROTECTED]
Verified Express, LLC "Making the Internet a Better Place"
http://www.verex.com/
