Saturday, March 29, 2008


When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows.

Don't forget to use an ORDER BY clause while using LIMIT. Why?

It's because it will prevent you to get an unpredictable subset of the whole result set.
Say, you have 30 rows whole result set (i.e. the result set without LIMIT and OFFSET) First you provide LIMIT 2 OFFSET 15. Next you provide LIMIT 5 OFFSET 15. Now you cant say it with full confidence that there will be common rows in these two cases. Your natural intuition says it should be. But it may not be.

Why this happend?

It is because, the query optimizer takes LIMIT into account when making a query plan, so you may get different plans (yielding different row orders) depending on your provided LIMIT and OFFSET.

No comments: