469,649 Members | 1,241 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,649 developers. It's quick & easy.

Retreiving large query results in chunks

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
Jul 20 '05 #1
1 5067
"Guy Erez" wrote:
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


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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Guy Erez | last post: by
3 posts views Thread by Jeremy Howard | last post: by
6 posts views Thread by Greg | last post: by
6 posts views Thread by ArunPrakash | last post: by
1 post views Thread by Nawaz Ijaz | last post: by
12 posts views Thread by bisuvious | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.