ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: iBatis - MySQL pagination
Date Fri, 15 May 2009 17:12:12 GMT
I really think you should frame your problem more clearly before you come up
with a solution :)

Paging is an interesting thing and there really isn't a "one size fits all"
solution. It depends on your requirements. If you want help figuring out the
most performant way to handle paging in your project then you should define
how the paging will be used. At that point you can talk about the best
optimization strategy. Without that I think we are just throwing ideas at a
wall to see if they stick.

On a sidenote using select * from some_table is a generally a bad idea. You
really should specify the columns that you want returned.

Brandon


On Fri, May 15, 2009 at 11:59 AM, Alin Popa <alin.popa@gmail.com> wrote:

> 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.goodin@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 <bhaarat.s@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.meadors@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
>

Mime
View raw message