ORDER BY is always one of the slowest things you can do in a query because every record returned by WHERE (or HAVING if you're using GROUP BY) has to be compared to every other record for sorting. Only after ORDER BY is run, then LIMIT is applied, because LIMIT doesn't know which records to return until after they are sorted.
So to make this faster you either have to make WHERE return fewer rows (better), or let it return more but remove the ORDER BY.
-- Darren Duncan
At 10:49 PM +0200 9/4/04, hilaner wrote:
Hi all!
Since my database growed to more than 20 000 records, I have noticed that select limited to a few numer of records by LIMIT takes much more time than select limited to similar number of records by another WHERE condition. I use sqlite_get_table function.
In my case I have the following queries: (1) SELECT *, col_1 - col_2 WHERE col_3 = 0 AND col_4 > 0 ORDER BY col_0 ASC col_5 DESC LIMIT 40 OFFSET 0; (2) SELECT *, col_1 - col_2 WHERE col_3 = 0 AND col_4 > 0 AND col_6 = 5 ORDER BY col_0 ASC col_5 DESC;
And the (2) query executes much faster than (1), even it has another contition in WHERE section. (1) takes nearly the same time like (1) without LIMIT condition (full table select). Is it normal? What can I do to speed up (1) query?
(SQLite 2.8.15, one table of 16 columns and more than 20000 records, Windows 2000, VC++ 6.0)
Regards Adam
