“Limit 1000,25” vs “limit 25 Offset 1000”

Question

Recently I've found out that MySQL has a offset feature. I've been trying to find documentation about the results of offset, or the difference in between offset and the limit variant, but I can't seem to find what I'm looking for.

Lets say I have 10.000 rows in a table and I want 25 results, from row 1.000. As far as I got so far, I could do both to get the same result:

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

What I'd like to know is the difference between the two.

• Does this actually do the same or is my understanding wrong?
• Is one slower/faster in larger tables
• 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)

I have the feeling offset skips the first X rows found in the database, disregarding sorting and the where.

Answer 1

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