Why same query giving two different result?

Question

I have created two tables & inserted values as shown below .

Table 1

create table maxID (myID varchar(4));

insert into maxID values ('A001');
insert into maxID values ('A002');
insert into maxID values ('A004');
insert into maxID values ('A003');

Table 2

create table maxID2 (myID varchar(4) PRIMARY KEY);

insert into maxID2 values ('A001');
insert into maxID2 values ('A002');
insert into maxID2 values ('A004');
insert into maxID2 values ('A003');

When I execute query

SELECT myId, @rowid:=@rowid+1 as myrow 
FROM maxID, (SELECT @rowid:=0) as init
ORDER BY myrow desc
LIMIT 1;

I get output as

+++++++++++++
myid + myrow
+++++++++++++
A003 + 4
+++++++++++++

AND

When I execute query

SELECT myId, @rowid:=@rowid+1 as myrow 
FROM maxID2, (SELECT @rowid:=0) as init
ORDER BY myrow desc
LIMIT 1;

I get output as

+++++++++++++
myid + myrow
+++++++++++++
A004 + 4
+++++++++++++

The difference between two table is that in second table I have myID as PRIMARY KEY.

You can view above data/ result at www.sqlfiddle.com.

My Question is

Why I am getting two different results when query is same?

NOTE : This question is bit related to my old question Getting last record from mysql, where I almost got the answer and Yak informed me that the order of rows are not guaranteed. :(

Answer 1

This is because when the selected fieldset is totally included into a given index fieldset, this index is used to retrieve the data instead of the fullscan result.

Since indexes have a default sorting order, when raw tables datas don't, the extracted data using the index is therefore appearing in a different order than if it were from a full table scan.

In your case the 4th row is indeed the 4th one when you are using the primary key because internally mysql (oracle, sql server...) organized it this way to seek the data faster.

Please note that by chance, you might have obtained the same result in both queries, just because the default selected result order is indeed not proven to be related to the inserted order.

At last, let me warn you that if you planned to add an index with a specific order in mysql (such as described here) so the rows are retrieved in, let say, the DESC order, you could not do it since it is not an allowed feature yet in mysql:

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.