By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,651 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,651 IT Pros & Developers. It's quick & easy.

Retreiving large query results in chunks

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
"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.