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/
