ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alin Popa <alin.p...@gmail.com>
Subject Re: iBatis - MySQL pagination
Date Fri, 15 May 2009 16:59:55 GMT
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