Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

If this is a 'stupid' question and someone know any documentation enlightening the subject, please add them in the replies.
I have the feeling offset skips the first X rows found in the database, disregarding sorting and the where.

share|improve this question
    
The MySQL documentation explains this perfectly. Is there a reason you weren't interested in looking at it yourself? –  siride Jul 2 at 12:59
1  
"For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax." (from dev.mysql.com/doc/refman/5.5/en/select.html). –  siride Jul 2 at 13:01
    
Because this still doesn't answer my questions. From your quote (and their text on the page) I should conclude that offset is the same thing as the limit, only written differently, as if the LIMIT is the shorthand selector? –  Martijn Jul 2 at 13:05
1  
it's different syntax for expressing the same thing. Your other sub questions are completely different from the title and the first part of your post. –  siride Jul 3 at 2:18
    
I understand that if it's the same thing, the other queries will not be effected differently in either situation. I just wasn't convinced it was the same thing, that was the answer I was looking for, thanks. –  Martijn Jul 3 at 7:11

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
Thank you. This: "There is absolutely no difference in the statements" was exactly what I was looking for, and with that bit of info I can see that sorting or where's don't matter. Wasn't sure about wether it was the same thing, and in coding the devil is in the details, I'd like to know for sure :) –  Martijn Jul 3 at 7:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.