CODEWITHSUNDEEP
mysql
Liza
Liza

Reputation: 21

MySql returns different rows with the same query

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

Upvotes: 0

Views: 38

Answers (1)

Bohemian
Bohemian

Reputation: 424983

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.

Upvotes: 2

Related Questions