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 <[EMAIL PROTECTED]>, [EMAIL PROTECTED] 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.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
