Igor Tandetnik wrote:
Dennis Cote wrote:
If you have an index on col then it will also be very fast
regardless of the size of the table, *** if not it will do a single
table scan to find the three maximum values. ***
(emphasis mine).
That's obvious. What about the case when there is no index on col? You
specifically claimed SQLite can manage in "a single table scan" even
in this case. I find it rather difficult to believe - assuming that by
a "single table scan" you mean some O(N) operation. Maybe I
misunderstand your use of this term.
Igor,
Sorry, I misunderstood your question and my original statement wasn't as
clear enough.
Yes it does a single table scan, however all it does with each row is
insert it into a temporary table which it then sorts, just as you
described. I though you were suggesting that it would scan the entire
table M times, once for each of the M results you wanted.
Of course the sorting operation is N Log N which swamps the order N
table scan and the order M result selection steps.
Dennis Cote