MySQL ORDER, CONDITION in offset, limit

https://dba.stackexchange.com/questions/69537/limit-1000-25-vs-limit-25-offset-1000

up vote5down voteaccepted

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