CODEWITHSUNDEEP
mysql
edeesis
edeesis

Reputation: 21

MySQL returning fewer rows in Result Set than Limit

We have a query that runs like this:

select distinct(id) 
from TABLE 
where date_id >= :startDate 
and date_id <= :endDate 
limit :offset,5000

The issue is - sometimes this query returns 5000 rows, sometimes 4999 from what I can tell. The trick is also, yesterday, I could get a particular offset (125000) to return 4999 every time I ran it, but today, it's now gone back to 5000.

Any idea why this could be happening? I've read that MySQL's limit is just that, a limit, return AT MOST n numbers of rows, but what I want to understand is the actual mechanics behind when MySQL makes that decision.

Thanks

Upvotes: 2

Views: 1321

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

As we cannot reproduce your results (sometimes 4999 -v- mostly 5000) all I could offer is that one row less met the criteria when 4999 was returned. Note that the second parameter is a maximum, so if the there are no more rows to present it will stop at the last row and this can mean less rows than the second parameter are returned.

MySQL documentation says:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants...

...

With 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):

 SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

https://dev.mysql.com/doc/refman/5.7/en/select.html

Upvotes: 1

Related Questions