Implementing pagination with mysql (limit and offset)

Question

I am implementing a RESTful api that has a GET endpoint that needs to support pagination. To do so, I have implemented the standard limit and offset query parameters. When I get to the (mysql) database, I need to fetch the data in an ordered, consistent manner. I have attempted to use an 'order by' combined with a 'limit x, y' in my query, yet the expected results are not being returned.

DDL/DML

Here is the (only) script being used to create the tables/indexes (ddl) and insert the data (dml): https://gitlab.com/connorbutch/reading-comprehension/-/blob/9-list-all-assessments/reading-comprehension-server-quarkus-impl/src/main/resources/db/migration/V1.0.0__CreateAssessment.sql. The contents of the file is also given below:

CREATE TABLE ASSESSMENT(
  ISBN BIGINT NOT NULL
, AUTHOR_FIRST_NAME VARCHAR(32)
, AUTHOR_LAST_NAME VARCHAR(32)
, TITLE VARCHAR(128) NOT NULL
, NUMBER_OF_POINTS FLOAT NOT NULL
, IS_VERIFIED BOOLEAN DEFAULT FALSE
, READING_LEVEL FLOAT NOT NULL
, CREATED_TEACHER_ID MEDIUMINT
) ENGINE=INNODB DEFAULT CHARSET=latin1;


CREATE UNIQUE INDEX ASSESSMENT_X01 ON ASSESSMENT(
    ISBN
);

ALTER TABLE ASSESSMENT
ADD CONSTRAINT ASSESSMENT_PK PRIMARY KEY(
    ISBN
);

CREATE INDEX ASSESSMENT_X02 ON ASSESSMENT(
    TITLE
);

CREATE INDEX ASSESSMENT_X03 ON ASSESSMENT(
    CREATED_TEACHER_ID
);

INSERT INTO ASSESSMENT(ISBN, AUTHOR_FIRST_NAME, AUTHOR_LAST_NAME, TITLE, NUMBER_OF_POINTS, IS_VERIFIED, READING_LEVEL, CREATED_TEACHER_ID)
            VALUES(9781976530739, 'Herman', 'Melleville', 'Moby Dick', 65, FALSE, 10.8, 1);

INSERT INTO rc.ASSESSMENT(ISBN, AUTHOR_FIRST_NAME, AUTHOR_LAST_NAME, TITLE, NUMBER_OF_POINTS, IS_VERIFIED, READING_LEVEL, CREATED_TEACHER_ID)
            VALUES(0486282112, 'Mary', 'Shelley', 'Frankenstein', 22, FALSE, 12.0, 1);

INSERT INTO rc.ASSESSMENT(ISBN, AUTHOR_FIRST_NAME, AUTHOR_LAST_NAME, TITLE, NUMBER_OF_POINTS, IS_VERIFIED, READING_LEVEL, CREATED_TEACHER_ID)
            VALUES(1503275922, 'Joseph', 'Conrad', 'Heart of Darkness', 36, FALSE, 12.5, 1);

INSERT INTO rc.ASSESSMENT(ISBN, AUTHOR_FIRST_NAME, AUTHOR_LAST_NAME, TITLE, NUMBER_OF_POINTS, IS_VERIFIED, READING_LEVEL, CREATED_TEACHER_ID)
            VALUES(0679732764, 'Ralph', 'Ellison', 'Invisible Man', 30, FALSE, 9.8, 1);

SQL Query The query ran to retrieve entries from the assessment table can be found at: https://gitlab.com/connorbutch/reading-comprehension/-/blob/9-list-all-assessments/reading-comprehension-server-quarkus-impl/src/main/resources/sql.properties . For convenience, I've also included it below

GET_ALL_ASSESSMENTS=SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID FROM ASSESSMENT ORDER BY ISBN ASC
GET_ASSESSMENT_LIMIT_OFFSET=SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID FROM ASSESSMENT ORDER BY ISBN ASC LIMIT :LIMIT, :OFFSET
GET_ASSESSMENT_COUNT=SELECT COUNT(1) FROM ASSESSMENT

Expected Results My expected results are given below:

limit=1,offset=0 -> return list of size one with lowest isbn being returned
limit=1,offset=1 -> return list of size one with the second lowest isbn being returned
limit=1,offset=2 -> return list of size one with the third lowest isbn being returned
limit=1,offset=3 -> return list of size one with the fourth lowest isbn being returned
limit=1,offset=x (x>=4) -> return an empty list
limit=2,offset=0 -> return list of size two with two lowest isbn being returned
limit=2,offset=1 -> return list of size two with second/third lowest isbn being returned
limit=2,offset=2 -> return list of size two with third/fourth isbn being returned
limit=2,offset=3 -> return list of size one with the fourth lowest isbn being returned
limit=2,offset=x (x>=4) -> return empty list
limit=3,offset=0 -> return list of size three with three lowest isbn
limit=3,offset=1 -> return list of size three with second/third/fourth lowest isbn being returned
limit=3,offset=2 -> return list of size two with third/fourth lowest isbn being returned
limit=3,offset=3 -> return list of size one with fourth lowest isbn being returned
limit=3,offset=x (x>=4) -> return empty list
limit=x,offset=0 (x>=4) -> return list of size four, with all isbn in it
limit=x,offset=1 (x>=4) -> return list of size three with second/third/fourth lowest isbn being returned
limit=x,offset=2 (x>=4) -> return list of size two with third/fourth lowest isbn being returned
limit=x,offset=3 (x>=4) -> return list of size one with fourth lowest isbn being returned

Actual Results

limit=1,offset=0 -> empty list returned (sql ran SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID FROM ASSESSMENT ORDER BY ISBN ASC LIMIT 1, 0
limit=1,offset=1 -> list of size one (sql ran SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID FROM ASSESSMENT ORDER BY ISBN ASC LIMIT 1, 1)
limit=1,offset=2 -> list of size two
limit=1,offset=3 ->
limit=1,offset=x (x>=4) ->
limit=2,offset=0 ->
limit=2,offset=1 ->
limit=2,offset=2 ->
limit=2,offset=3 ->
limit=2,offset=x (x>=4) ->
limit=3,offset=0 -> empty list (sql ran * SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID FROM ASSESSMENT ORDER BY ISBN ASC LIMIT 3, 0*)
limit=3,offset=1 ->
limit=3,offset=2 ->
limit=3,offset=3 ->
limit=3,offset=x (x>=4) ->
limit=x,offset=0 (x>=4) ->
limit=x,offset=1 (x>=4) ->
limit=x,offset=2 (x>=4) ->
limit=x,offset=3 (x>=4) ->

Questions:

Am I creating the sql correctly?
If I am, is this an issue with mysql/how would I remedy this to produce the expected results?
If I am not, how do I correct my query to produce the expected results? Is it as simple as flipping my limit/offset?
Any general input on how to implement this functionality would be welcome.

Thanks, Connor

Nicely crafted question with clearly defining the requirement ! — Soumendra Mishra
– Soumendra Mishra, Commented Sep 12, 2020 at 16:10

Sujitmohanty30 · Accepted Answer · 2020-09-12 15:47:21Z

1

Full marks to the question preparation,

If I see the problem and understood correct, the issue is with the order you place limit and offset

There are two ways,

Either you explicitly mention the limit and offset as,

SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID 
FROM ASSESSMENT 
ORDER BY ISBN ASC LIMIT 1 OFFSET 0;

OR

Incase you don't want to mention the word offset explicitly we need to pass the offset value first and then limit value as

SELECT ISBN,AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME,TITLE,NUMBER_OF_POINTS,IS_VERIFIED,READING_LEVEL,CREATED_TEACHER_ID 
FROM ASSESSMENT 
ORDER BY ISBN ASC LIMIT 0,1;

P.S. I didn't test each and individual cases as you mentioned but I think if my understanding correct you know what to do now.

answered Sep 12, 2020 at 15:47

Sujitmohanty30

3,3362 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Connor Butch Over a year ago

Thanks Sujitmohanty30, I had went ahead and flipped the parameters, and it looks like that fixed the problem. This confirms your answer. Thanks for your help and confirmation! (accepted and upvoted)

Sujitmohanty30 Over a year ago

@Connor Butch, may I know the reason of un-accept the answer ? Was I wrong ? You have confirmed and accepted the answer ? Seems to be not right behaviour. Sorry to say

Connor Butch Over a year ago

Someone else commented, and I accepted theirs as well, which I guess unaccepted yours (I'm not on here that often, so I didn't know you could only accept one answer at once). I've accepted yours again, as it was first.

Sujitmohanty30 Over a year ago

@Connor Butch, If I understand your action, you cannot accept two answers at same time but rather you can only up-vote any answer which made contribution and it's totally your choice. You can un-accept an answer after accepting it but with giving proper reasons ( as I am saying this is not a hard and fast rule but doing things in better way what I feel personally). Thank you for your response and I hope you understand

Jpsh · Accepted Answer · 2020-09-12 15:49:33Z

1

you have it backwards LIMIT *OFFSET*, *ROW_COUNT* alternatively you can use OFFSET

MySQL Documentation

So query could be

SELECT * FROM Table LIMIT 0, 1

Or

SELECT * FROM Table LIMIT 1 OFFSET 0

MySQL Documentation

answered Sep 12, 2020 at 15:49

Jpsh

1,72612 silver badges17 bronze badges

1 Comment

Connor Butch Over a year ago

Thanks Jpsh! This confirms our above findings (I've upvoted and accepted this answer as well)

Collectives™ on Stack Overflow

Implementing pagination with mysql (limit and offset)

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related