In article
<OF8DF236E7.BF0FAD30-ON80256FFD.004F07DD-80256FFD.0050A301__3137.66894764758$1115735646$gmane$org@stripped>,
Alec.Cawley@stripped writes:
>> CREATE TEMPORARY TABLE tbl1 (
>> id INT UNSIGNED NOT NULL,
>> val INT UNSIGNED,
>> PRIMARY KEY (id),
>> UNIQUE KEY (val)
>> );
>>
>> INSERT INTO tbl1 (id, val) VALUES (1, 1);
>> INSERT INTO tbl1 (id, val) VALUES (2, 2);
>> INSERT INTO tbl1 (id, val) VALUES (3, 3);
>> INSERT INTO tbl1 (id, val) VALUES (4, 4);
>>
>> SELECT * FROM tbl1;
>>
>> DELETE FROM tbl1 WHERE id = 3;
>>
>> INSERT INTO tbl1 (id, val) VALUES (5, 5);
>>
>> SELECT * FROM tbl1;
>>
>> The first SELECT happens to return 1/2/3/4, but the second one returns
>> for me 1/2/5/4.
> InnoDB would probably do this, but MyISAM probably woudl not.
Incorrect, at least for the MySQL server I tested.
> If it
> chooses to do a fill table scan, it will deliver the results iht the
> essentially random order it stores them. If it uses and index, it is qitel
> likely to deliver them in the order of that index - which may not be the
> primary key. Indeed, the optimiser theoretically might use different
> indexes for the same query on different days, as the table cnages.
Yes. I think the difference is not InnoDB vs MyISAM, but "SELECT
pkey" vs "SELECT pkey. someothercol". In the first case the result
set can be built by just looking at the index (which is of course
sorted), whereas the second case also needs to look at the table
itself (which is unsorted).
> It is therefore *never* safe to assume any sort of ordering unless you
> specify it.
That's what I wanted to emphasize.
