CODEWITHSUNDEEP
mysqlpaginationlimit
user989990
user989990

Reputation: 175

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?

Upvotes: 1

Views: 3304

Answers (5)

Vismari
Vismari

Reputation: 745

Limit clause takes one or two arguments:

the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).

So you can do:

select * from table limit 0,50000;
select * from table limit 50001,50000;
select * from table limit 100001,50000;

Take a look at this link

Upvotes: 1

Marc B
Marc B

Reputation: 360572

It's LIMIT offset, rowcount. Your first query fetches 50,000 rows, starting at row 0. Your second query fetches 50,000 rows, starting at row 2. In other words, you're fetching 49,999 same rows, and then one different.

If you want to split your 116,000 rows into 3 equal parts, you'd have to do

SELECT ... LIMIT 0, 38666
SELECT ... LIMIT 38667, 38666
SELECT ... LIMIT 77333, 38668

Upvotes: 1

John Watson
John Watson

Reputation: 2573

The first argument to LIMIT is the starting record number, not the starting page number. Your limits in this case should be:

  1. "SELECT * FROM table LIMIT 0, 50000" # 50,000 records starting at the 0th record
  2. "SELECT * FROM table LIMIT 50000, 50000" # 50,000 records starting at the 50000th record
  3. "SELECT * FROM table LIMIT 100000, 50000" # 50,000 records starting at the 100000th record

Upvotes: 2

Polynomial
Polynomial

Reputation: 28316

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.

Upvotes: 6

Patrick B.
Patrick B.

Reputation: 12333

Yes, you're understanding is wrong. You should do 

SELECT * FROM table LIMIT 100000, 50000

. The syntax is either

SELECT [..] LIMIT <count>

or 

SELECT [..] LIMIT <offset>, <count>

. Whereas everything is relative to the number of rows SELECTed.

Upvotes: 0

Related Questions