On Sat, 4 Sep 2004, Darren Duncan wrote:
>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.
It's not a good idea to use LIMIT on unordered results, of course, as the
order of results for unordered result sets is, err, unspecified, hence you
will not get predictable results.
LIMIT is a big win on client/server databases, where the bandwidth saved
against returning a full result set is significant.
More info inline...
>
>-- 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?
Query (2) has an extra condition in the WHERE clause, thus reducing the
result set size to be sorted. As sorting is probably an O(n.log(n))
operation, halving the result set will more than halve the time taken to
sort, for example. Add that extra condition to query (1), and you should
get similar results for both queries.
>>
>>(SQLite 2.8.15, one table of 16 columns and more than 20000 records, Windows
>>2000, VC++ 6.0)
>>
>>Regards
>>Adam
>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
