MySQL pagination

by esso   Last Updated August 31, 2017 21:05 PM

My question is about using pagination using table_id, offset and limit to reduce overhead of MySQL. Using the table id, to my understanding, the server will not read all the records up to offset + limit and than take i table_id larger than will cause the server to read only records larger than table_id.

I have a web server which sends to mobile app list of products. The products are stored in MySQL server. PK for the table is productPoC_id . I am sending the products to mobile app in paging.

Page size is 500 records. I am using following select statement SELECT * FROM productPoC WHERE productPoC_id > @productId ORDER by productPoC_id ASC LIMIT @limit OFFSET @offset. In first call I set productId to 0, limit to 500 and offset 0. MySQL returns me correctly the records (productPoC_id is from 1 to 500). The problem is in second call. In second call I set productId to 500, and offset to 500. In this case MySQL returns me productPoC_id 1000 instead of 501 (note that I serialized all the productPoC_id from 1 to 150000). I would expect MySQL to return productPoC_id 501. When I remove the offset from the select, productPoC_id correctly (starting from 501).

I am trying to understand why in the first sample I am not getting the right productPoC_id.


Related Questions

filtering data from db

Updated April 23, 2015 21:02 PM

Am I planning my database/backend correctly?

Updated April 08, 2015 18:02 PM

foreign key with null value in MySQL

Updated January 22, 2018 19:05 PM