weaver weaver - 10 months ago 49
MySQL Question

Why the id is diffrent between 'select *' and 'select id' in MySQL?

I have a table named

account_info
.
When I run
select id from account_info limit 0, 10
,the result is as follows.

ids

But when I run
select * from acccount_info limit 0, 10
, the result is as follows(Other columns are ignored).

ids

Why the ids are different?
When I add
order by id
to these two sql, the results are same. It seems like is the order of result that matters. But I think when there is no
order by id
, both
select id from account_info limit 0, 10
and
select * from account_info
share the same default sort policy, and the result supposed to be same.

Can somebody explain why? My MySQL version is 5.7.14.

Answer Source

There is no "default sort policy" as sorting always requires some (usually quite expensive) computation, so if you don't specify any order MySQL is free to return the rows the way it thinks is fastest / cheapest.

The reason why you see the values being sorted when you only query id is that in this case MySQL just needs to read the primary key which is stored as a (sorted) B-Tree. You can confirm this by looking at the query execution plan - it should display Using index which is described as :

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

When you query * then MySQL has to go through the table data itself which can be stored in any order.

Please note that the above explanation is no guarantee on result sorting. To repeat: without an explicit ORDER BY all bets are off on how the data is returned.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download