mysqlcleardb

MySQL LIMIT returning incorect set of rows

(Running on MS Azure cloud version of MySQL - CleadDB)

I have a simple query:

SELECT idx, date_added FROM my_table ORDER BY idx ASC LIMIT 100752, 10

I am expecting to get next 10 records after the idx 100752 (if present). However I get totally different records i.e.:

enter image description here

I verified the records are present so I ran the query again, this time with the records I know are there i.e.:

SELECT idx, date_added FROM my_table ORDER BY idx ASC LIMIT 102366, 10

yet I get the following unmatching rows again.

enter image description here

I know I am probably doing something silly. Help appreciated.

EDIT/UPDATE: I followed some of the suggestions below and tested the results by using specific WHERE predicate rather then LIMIT function. Please see the results below. Both queries should return the same result - or so I would expect.

TEST 1: 

SELECT idx, date_added  FROM attachments ORDER BY idx ASC LIMIT 67805, 20

returns: enter image description here

TEST 2:

SELECT 
    idx, date_added 
FROM attachments 
WHERE
    idx >=67805 and idx <67825
ORDER BY idx ASC

returns: enter image description here

I would expect both to be the same but this is not the case.

SOLUTION: The LIMIT does not care about the primary auto-increment key. It cares about the number of rows (regardless of their index) so even if the idx is SORTed - when some rows are missing (meaning there were some deletes in the past) it will offset the results based on the number of deleted rows. Thx All.

Solution

  • You may have gaps in your idx column. That is, some values of idx were once used but no longer exist in the table. As such the row number is not the same as idx (LIMIT offset, row_count works on row number not idx)

    Why don't you try this query instead?

    SELECT 
    idx, date_added 
FROM my_table 
WHERE
    idx >=100752 and idx <100762
ORDER BY idx ASC