I'm running the next queries:
1. select * from page LIMIT 10;
2. select page.id from page LIMIT 10;
"id" is the primary key in this table. How it can be possible that MySql returns different rows?
I'm using MySql 8.19
-
you can use ORDER BY id LIMIT 10Rahul Biswas– Rahul Biswas2021-09-21 16:16:28 +00:00Commented Sep 21, 2021 at 16:16
-
I know. But it's very interesting why does it happening in such a way? TYLiza– Liza2021-09-21 16:17:30 +00:00Commented Sep 21, 2021 at 16:17
-
2Relational databases do not store data sorted, you have to sort it if you want consistent resultsRiggsFolly– RiggsFolly2021-09-21 16:17:40 +00:00Commented Sep 21, 2021 at 16:17
-
you'll get data based on logical ordering.Rahul Biswas– Rahul Biswas2021-09-21 16:19:28 +00:00Commented Sep 21, 2021 at 16:19
-
Could you give me, please, a link where I can read more about it?Liza– Liza2021-09-21 16:22:43 +00:00Commented Sep 21, 2021 at 16:22
|
Show 1 more comment
1 Answer
In the absence of an order by clause, row order is arbitrary, but typically whatever is the most convenient for the database engine - often in the order rows are laid out on disk, or whatever rows are in cache memory, etc.
However, for queries that refer only to indexed columns, which would be the case for the id column query, databases usually perform an index only scan. Without an order by clause, such a scan would typically return rows in the order the index values are laid out on disk, not the order the rows are laid out on disk. Hence the difference.
