Here, off the top of my head are situations in mysql where you can trust
that the data is ordered in some fasion.
1. Using an order by clause on a query.
2. Using a group by the data will come out in ascending order of the
column that was grouped on.
3. alter table order by has been performed and the table hasn't been
modified .
4. select key from t; that uses the 'Using Index' in explain will return
in the order of the key.
Harald Fuchs wrote:
>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.
>
>
>
>
