Unfortunately this doesn't work well if you want to tell your users how many pages were found in the query. Sure, you could use SQL_CALC_FOUND_ROWS with FOUND_ROWS(), but this will defeate the purpose of the LIMIT clause -- speed -- because MySQL will have to figure out all the results of the query. Worse, MySQL will not be able to cache the query because you're changing the query string with each paging requsts.
--- mos <[EMAIL PROTECTED]> wrote: > Carlos, > Apart from using the proper indexes, you > should only retrieve the > number of rows that are going to be displayed on the > page at one time. If > you are displaying 10 rows on the page, why retrieve > 1000 rows? The user > would have to page through it 100x and you have to > ask yourself, is this > likely going to happen for every user? If not, then > you are wasting CPU cycles. > > It is better to use "Select ... 10,10" > which is the offset, limit. > Keep increasing the offset (starting at 0) for each > time the user presses > "Next page". MySQL will then retrieve only 10 rows > at a time. This is 100x > faster than retrieving all 1000 rows at a time. > > Mike > > At 04:49 PM 3/9/2005, Carlos Savoretti wrote: > >Hi all: > > > >I programming a GUI which retrieve big tables > oftenly. > > > >So, I retrieve chunks of 1000 rows and paginate > then > > > >to browse the entire table. It works fine, but it's > rather > > > >slow. > > > >I would like to know if I could set some option > thru > > > >mysql_option() to optimize the client side > (mysql-client-3.23.58) > > > >and what is the the recommended value to clamp the > `page' for a > > > >gui app. (For 1000 rows it uses about 12 seconds). > > > >Thanks a lot... > > > >-- > >Carlos Savoretti <[EMAIL PROTECTED]> > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
