On Thu, Apr 10, 2008 at 09:56:31PM -0500, Dewey Gaedcke scratched on the wall:
> Set @a = 0; > Select T1.rownumber, T1.Col2 from > ( > Select @a:[EMAIL PROTECTED] as rownumber, Col1, Col2 > from Table where Col1 = 'abc' Order by Col1 > ) as T1 > Where T1.rownumber between 21 and 30; As I understand it, most systems are going to do a full table scan for that sub-select. You're then using the outer select and the rownumber to essentially implement an LIMIT/OFFSET. If my assumptions about the full table scan are correct, this is extremely inefficient-- but if it works, it works. If you're getting the kind of performance you need, I think it would be much easier to just get rid of the outer select and put a LIMIT/OFFSET directly on the subselect. For example, the query above would turn into this: SELECT Col2 FROM Table WHERE Col1 = 'abc' ORDER BY Col1 LIMIT 10 OFFSET 20; This is still pretty inefficient when the offset starts to get large, but at least the query will terminate as soon as the limit is hit. I don't think that's the case for the sub-select you've got setup. So this is not the best, but it is still better than the original. (I also assume this is a contrived example, because as-written the ORDER BY clause is pointless thanks to the WHERE clause.) -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
