Search code examples
mysqlpaginationlimit

mySQL Pagination Using Limit Retrieves Duplicate Rows

I have a table with 116,000 rows and I want to retrieve all of these rows but in 3 separate queries using:

  1. "SELECT * FROM table LIMIT 0, 50000"
  2. "SELECT * FROM table LIMIT 1, 50000"
  3. "SELECT * FROM table LIMIT 2, 50000"

I have tried this but all the queries return a result of 50,000 rows, even if the "LIMIT is 10, 50000" there is a result which is not what I want. I am only expecting 16,000 rows for the 3rd query.

How can I accomplish this? Is my expectation of LIMIT pagination wrong?

Solution

  • Your expectation of how LIMIT works is wrong. The first parameter is the first record to fetch, and the second parameter is the number of records to fetch.

    For example, if a query has 800 results and you use LIMIT 100, 300 you will get the 100th to 400th rows, totalling 300 rows.

    Go to http://dev.mysql.com/doc/refman/5.0/en/select.html and search for "The LIMIT clause" to read more about how LIMIT works.