Retreiving large query results in chunks

[Guy Erez]

Hi,

I'm running queries with MySql 4.0.17 that return thousands of
records. Because I need to present them in GUI, I returieve the
results in chunks using LIMIT, for example - get first 100, then the
range 100-2000 and so on.

The problem is as follows: in the first chunk, MySQL uses one strategy
to fetch the results, and in the following chunks - a different
strategy.
This means that records from the subsequent queries might have records
that already appeared in the first query or that some records will be
left out.

For performance issues it is a problem to add a unique secondary
sorting criteria (like id) to the query.

Is there a clean way to force MySQL to relate to the first (initial)
query result set?

Thanks,
Guy

[steve]

Re: Retreiving large query results in chunks

"Guy Erez" wrote:[color=blue]
> Hi,
>
> I’m running queries with MySql 4.0.17 that return thousands of
> records. Because I need to present them in GUI, I returieve the
> results in chunks using LIMIT, for example - get first 100, then[/color]
the[color=blue]
> range 100-2000 and so on.
>
> The problem is as follows: in the first chunk, MySQL uses one[/color]
strategy[color=blue]
> to fetch the results, and in the following chunks - a different
> strategy.
> This means that records from the subsequent queries might have[/color]
records[color=blue]
> that already appeared in the first query or that some records will[/color]
be[color=blue]
> left out.
>
> For performance issues it is a problem to add a unique secondary
> sorting criteria (like id) to the query.
>
> Is there a clean way to force MySQL to relate to the first[/color]
(initial)[color=blue]
> query result set?
>
> Thanks,
> Guy[/color]

One way of doing it is to do a full query (not limiting it), and save
the result set to a table. One must select a unique index to be saved
alongside the result set, so that multiple query results can be
distinguished. Typically this is done using session id (SID).

Then simply page thru this set from the db.

That’s how phpbb does it.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Retrei...ict134466.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=450380