MySQL offset infinite rows

Question

I would like to construct a query that displays all the results in a table, but is offset by 5 from the start of the table. As far as I can tell, MySQL's LIMIT requires a limit as well as an offset. Is there any way to do this?

Answer 1

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;

Answer 2

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

Answer 3

As noted in other answers, MySQL suggests using 18446744073709551615 as the number of records in the limit, but consider this: What would you do if you got 18,446,744,073,709,551,615 records back? In fact, what would you do if you got 1,000,000,000 records?

Maybe you do want more than one billion records, but my point is that there is some limit on the number you want, and it is less than 18 quintillion. For the sake of stability, optimization, and possibly usability, I would suggest putting some meaningful limit on the query. This would also reduce confusion for anyone who has never seen that magical looking number, and have the added benefit of communicating at least how many records you are willing to handle at once.

If you really must get all 18 quintillion records from your database, maybe what you really want is to grab them in increments of 100 million and loop 184 billion times.

Answer 4

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.

Answer 5

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.

Answer 6

You can use a MySQL statement with LIMIT:

START TRANSACTION;
SET @my_offset = 5;
SET @rows = (SELECT COUNT(*) FROM my_table);
PREPARE statement FROM 'SELECT * FROM my_table LIMIT ? OFFSET ?';
EXECUTE statement USING @rows, @my_offset;
COMMIT;

Tested in MySQL 5.5.44. Thus, we can avoid the insertion of the number 18446744073709551615.

note: the transaction makes sure that the variable @rows is in agreement to the table considered in the execution of statement.

Answer 7

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()

Answer 8

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.

Answer 9

I know that this is old but I didnt see a similar response so this is the solution I would use.

First, I would execute a count query on the table to see how many records exist. This query is fast and normally the execution time is negligible. Something like:

SELECT COUNT(*) FROM table_name;

Then I would build my query using the result I got from count as my limit (since that is the maximum number of rows the table could possibly return). Something like:

SELECT * FROM table_name LIMIT count_result OFFSET desired_offset;

Or possibly something like:

SELECT * FROM table_name LIMIT desired_offset, count_result;

Of course, if necessary, you could subtract desired_offset from count_result to get an actual, accurate value to supply as the limit. Passing the "18446744073709551610" value just doesnt make sense if I can actually determine an appropriate limit to provide.

Answer 10

WHERE .... AND id > <YOUROFFSET>

id can be any autoincremented or unique numerical column you have...