I wanted to say that is retrieving all the data from database (into memory) and after that is iterating over it. What is happening when "SELECT * FROM mytable" is returning 5 MIL records ? For sure I don't want that. I don't know if mysql knows how to do optimization without "helping" it somehow (LIMIT or something else).
On Fri, May 15, 2009 at 7:52 PM, Brandon Goodin <brandon.goo...@gmail.com> wrote: > Where are you seeing it perform a full table scan? select * does not > *necessarily* mean it will load all of your records into memory. Oracle for > example is smart about this and I would assume that most databases have > followed suit in this sense over the last few years. Most databases will > smartly use their indexes to determine what gets loaded and how much. It's > really the reason why you don't know how large your resultset is until it is > actually fully loaded. You'll be guaranteed a full table scan if you were to > sort on a column that is derived from a function call. > > Another route on this may be to actually look at you indexing strategy. > > Brandon > > > On Fri, May 15, 2009 at 11:27 AM, Bhaarat Sharma <bhaara...@gmail.com> > wrote: >> >> what if we have a stored procedures that takes in 'startrow' and 'endrow' >> as parameters. Based on those it returns the results back. >> If a sp like that were to be called using iBatis with pagination then will >> the SP be called again to get NEXT set of results? >> >> On Fri, May 15, 2009 at 12:15 PM, Larry Meadors <larry.mead...@gmail.com> >> wrote: >>> >>> Do it in the sql statement instead of using pagination in ibatis. >>> >>> Look at limit, row_count and offset here: >>> >>> http://dev.mysql.com/doc/refman/5.0/en/select.html >>> >>> You will probably need to use $substitution$ for the parameters, but >>> surely some mysql stud can tell you more about that than I can. :) >>> >>> Larry >> > > -- Best Regards, Alin
