Re: circumventing limitations on the "Liimit" clause

[Dewey Gaedcke]

The "order by" substring clearly forces the DB to build the full result set BEFORE sorting so WHY does the parser WAIT to run the @i:=@i+1 assignment until AFTER the sort run??? I'm happy that it does this, but WHY doesn't make any sense to me.........

Regarding scaling, right now it's scanning 12,000 rows and returning me 196 in .02 seconds but I'm VERY INTERESTED in the most scalable way to do this. I'm trying NOT to do it in Java because:
1) there is some data intensive pre-processing that I didn't want to send to the app server
2) there are multiple different queries to run depending upon results of the pre-processing
3) I felt like a stored proc would have it's execution plan cached, rather than having to be parsed, optimized, etc on each Query sent from Java

All feedback welcome!!