MySQL offset infinite rows

From the MySQL Manual on LIMIT:

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95, 18446744073709551615;

As you mentioned it LIMIT is required, so you need to use the biggest limit possible, which is 18446744073709551615 (maximum of unsigned BIGINT)

SELECT * FROM somewhere LIMIT 18446744073709551610 OFFSET 5

Another approach would be to select an autoimcremented column and then filter it using HAVING.

SET @a := 0; 
select @a:=@a + 1 AS counter, table.* FROM table 
HAVING counter > 4

But I would probably stick with the high limit approach.

As others mentioned, from the MySQL manual. In order to achieve that, you can use the maximum value of an unsigned big int, that is this awful number (18446744073709551615). But to make it a little bit less messy you can the tilde "~" bitwise operator.

  LIMIT 95, ~0

it works as a bitwise negation. The result of "~0" is 18446744073709551615.

I ran into a very similar issue when practicing LC#1321, in which I have to select all the dates but the first 6 dates are skipped.

I achieved this in MySQL with the help of ROW_NUMBER() window function and subquery. For example, the following query returns all the results with the first five rows skipped:

SELECT
    fieldname1,
    fieldname2
FROM(
    SELECT
        *,
        ROW_NUMBER() OVER() row_num
    FROM
        mytable
) tmp
WHERE
    row_num > 5;

You may need to add some more logics in the subquery, especially in OVER() to fit your need. In addition, RANK()/DENSE_RANK() window functions may be used instead of ROW_NUMBER() depending on your real offset logic.

Reference:

MySQL 8.0 Reference Manual - ROW_NUMBER()

Just today I was reading about the best way to get huge amounts of data (more than a million rows) from a mysql table. One way is, as suggested, using LIMIT x,y where x is the offset and y the last row you want returned. However, as I found out, it isn't the most efficient way to do so. If you have an autoincrement column, you can as easily use a SELECT statement with a WHERE clause saying from which record you'd like to start.

For example, SELECT * FROM table_name WHERE id > x;

It seems that mysql gets all results when you use LIMIT and then only shows you the records that fit in the offset: not the best for performance.

Source: Answer to this question MySQL Forums. Just take note, the question is about 6 years old.