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.