mysql select statement with limit offset bigger than actually records

Question

I create an mysql-statement that paginates the results using LIMIT x, n (where x is the offset and n the returned entries).

The offset is created using GET-Vars in the form of page=x.

Now Google has some strange entries in its index that come from old crawls, where the page-variable exceeds the actual amount of records in the result-set.

Means, the query created with the page variable results in something like LIMIT 1000, 30 - but the query will only return 900 entries (since the content of the table changed meanwhile. This returns an empty result-set, of course.

Is there a way to tell mysql, that if the offset exceeds the returned records to just show the last possible result-set? I don't want to make an extra query using COUNT() first, since this would double the load on the mysql-server (right now I'm using SQL_CALC_FOUND_ROWS to determine the total amount of records the query would return without the LIMIT-Statement.

Is there any way you can avoid using MySQL to enforce this? Could you put this into your business logic? — Tim Biegeleisen, Jul 21 '15 at 10:19
it is a problem to create another query if there is no results? Change order then and you have last records. I dont think so that is other solution, without counting records — Daimos, Jul 21 '15 at 10:23
This sounds like it's more a problem with how your paging system is setup. — Hayden, Jul 21 '15 at 11:58

Marcello Mönkemeyer · Accepted Answer · 2015-07-21T14:04:44.573

2

Get all requested rows, with at least one row (MySQL 5.6):

SELECT *
FROM `table`
LIMIT 1000, 30
UNION ALL (
    SELECT *
    FROM `table`
    WHERE FOUND_ROWS() = 0
    ORDER BY `id` DESC
    LIMIT 1
)

SQL Fiddle

edited Jul 21 '15 at 14:04

answered Jul 21 '15 at 10:47

Marcello Mönkemeyer

652
5
30

I tried this query, when the first SELECT contains records that fit into the limit offset then only 1 row is returned. When the limit offset yield no results, the UNION will return 1 row on every odd call and 0 on every even call (it alternates between 0 / 1 rows when you "refresh"). – N.B. Jul 21 '15 at 11:27
Hm, that's really strange. I tested it myself now and got the same strange behaviour. It seems to work with a `LIMIT 1000`, but not with a `LIMIT 0, 1000`, which is obviously the same. That kind of makes no sense and seems to be a bug to me. I'll investigate on this, thanks for the hint. – Marcello Mönkemeyer Jul 21 '15 at 11:33
Yep, sadly it doesn't work. I tried it because it seemed like a cool solution. If you get it to work or find what happens, it'd be great to know. – N.B. Jul 21 '15 at 12:08
I forgot the parentheses around the second `SELECT`, that's why the second `LIMIT` affected the combined result instead of just the second `SELECT`. Stupid me. But I think it's working as expected now. Tested under MySQL 5.6. and also added a working SQL Fiddle. – Marcello Mönkemeyer Jul 21 '15 at 13:40
This time the first SELECT works as intended when there are records (the offset isn't too large). When the offset is too big and first one returns no records, I still get alternating results for the select in the UNION (1 row first time, 0 rows 2nd time and so on). – N.B. Jul 21 '15 at 13:45
That's strange, cauz in [this SQL Fiddle](http://sqlfiddle.com/#!9/1ac728/3) it's working even with two SELECTs. Which MySQL version are you using? – Marcello Mönkemeyer Jul 21 '15 at 14:01
I ran the query on `5.5.41-MariaDB-1~precise` and on `5.6.19-0ubuntu0.14.04.1` and both yield the same result as described above. SQLFiddle gives the correct result for `5.6` but no records for `5.5`. – N.B. Jul 21 '15 at 14:01
This sounds interesting - I'll try it... thanks a ton! – Swissdude Jul 22 '15 at 11:41

mysql select statement with limit offset bigger than actually records

1 Answers1