MySQL – Difference Between ‘LIMIT 0, 1’ and ‘LIMIT 1’

MySQL

I recently stumbled upon example codes, which differed by these notations.

SELECT * FROM table LIMIT 0, 1
SELECT * FROM table LIMIT 1

The first argument should be considered as the offset if I'm not wrong, the purpose of those two queries is to limit the selection to up the first row of table

Is there any negative effect when leaving out the offset/how is it possible to leave it out in the first place? Or did I misunderstood the queries?

Best Answer

As the 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 (except when using prepared statements).

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

The LIMIT clause is used in the SELECT statement to constrain the number of rows in a result set. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integer constants.

The following illustrates the LIMIT clause syntax with 2 arguments:

SELECT * FROM tbl
LIMIT offset, count;

Let’s see what the offset and count mean in the LIMIT clause:

The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
The count specifies maximum number of rows to return.

When you use LIMIT with one argument, this argument will be used to specifies the maximum number of rows to return from the beginning of the result set.

SELECT * FROM tbl LIMIT count;

The query above is equivalent to the following query with the LIMIT clause that accepts two arguments:

SELECT * FROM tbl LIMIT 0, count;

The LIMIT clause often used with ORDER BY clause. First, you use the ORDER BY clause to sort the result set based on a certain criteria, and then you use LIMIT clause to find lowest or highest values.

Related Solutions

MySQL Stored Procedure – Loop Through Table and Delete Rows

There are two possible solutions:

set @z to null in the very beginning of your loop (prior to SET @sql_text2 = concat('....)

or instead of

SELECT id INTO @z FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1;

use this:

SET @z = (SELECT id FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1);

Explanation:

When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO that won't reset the value if nothing is found.
see: https://dba.stackexchange.com/a/35207/12923

MySQL – Limit 1000,25 vs Limit 25 Offset 1000

In terms of operation

SELECT id,name,description FROM tablename LIMIT 1000,25
SELECT id,name,description FROM tablename LIMIT 25 OFFSET 1000

there is absolutely no difference in the statements

siride's comment:

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

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

is exactly the point.

LIMIT 1000,25 means LIMIT 25 OFFSET 1000

From the same Documentation

LIMIT row_count is equivalent to LIMIT 0, row_count

YOUR ACTUAL QUESTIONS

Does this actually do the same or is my understanding wrong?

Is one slower/faster in larger tables

Since both queries are the same, there is no difference

Does the result of offset change when I do WHERE column=1 (say column has >100 different values)

Does the result of offset change when I do ORDER BY column ASC (asuming it has random values)

Using LIMIT does not change any result sets. They simply navigate within the result set.

This query

SELECT id,name,description FROM tablename ORDER BY id LIMIT 1000,25

would be different from

SELECT * FROM (SELECT id,name,description FROM tablename LIMIT 1000,25) A ORDER BY id;

because the LIMIT is being applied at a different stage.

The first query returns nothing if tablename has less 1000 rows

The second query returns nothing if the subquery has less 1000 rows

CONCLUSION

You will have to sculpt the query to make sure you are sorting data at the right stage