3

To my understanding, in MySQL/Postgres database there is no guarantee for the order of values with a select statement without an order-by clause, thus one should be getting rows arbitrarily. If so, then why are there usually no duplicates when I perform such query?

SELECT * FROM `table` LIMIT 0,5
SELECT * FROM `table` LIMIT 5,5
SELECT * FROM `table` LIMIT 10,5

Aren't these queries safe to use, considering that one only cares about not fetching duplicates and does not mind about the ordering? If so, how then?

0

2 Answers 2

6

Your syntax won't work with PostgreSQL, but I can answer your question for PostgreSQL anyway:

Your query will often work as you intend because the three statements will often use the same execution plan. There is no guarantee for that though.

One possibility where even a sequential scan in PostgreSQL will return a different order is if there is already a sequential scan of the table in progress: Then PostgreSQL will start scanning where the other statement is currently scanning the table, i.e. somewhere in the middle, so that both scan can execute somewhat synchronized to reduce the I/O operations necessary.

1
  • "Your query will often work as you intend because the three statements will often use the same execution plan. There is no guarantee for that though." Indeed as standards defined SQL is to be orderless in tables, delivered tables/resultsets.. Pagination of anny kind requires a deterministic (fixed) sort.. The ORDER BY should atleast include one column in the ORDER BY which has a PRIMARY or UNIQUE key.. to make to deterministic (fixed) on every run.. A non deterministic (random) sort can still give invalid results.. Commented Sep 2, 2019 at 11:59
2

If DELETES could be happening concurrently, OFFSET will lead to duplicates! concurrent INSERTs or UPDATEs have other potential problems.

Furthermore, OFFSET involves scanning that many rows. As you get farther and farther into the table, this gets slower and slower. (It's not an issue for LIMIT 10,5, but it is an issue for LIMIT 100000,5.)

So, what to do? "Remember where you left off" and get the next 5 rows. AND use an ORDER BY, preferably the PRIMARY KEY. See this for discussion of the performance and bugs, plus how to deal with "left off" for a composite PK.

Note: In MySQL (InnoDB), using the PK is better than using a UNIQUE key because you are doing SELECT * and want the data. The data is clustered with the PK.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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