Search code examples
mysqlsqlpaginationlimitoffset

Implementing pagination with mysql (limit and offset)

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:

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

Thanks, Connor

Solution

  • 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.