To all who answered thank you. This answer below is the one that I can
use to convince him what he proposes is not necessarily safe.
Now I just need to decide how to convince him it was his idea :-)
Boyd E. Hemphill
O: (512) 248-2287
M: (713) 252-4688
From: Michael Stassen [mailto:Michael.Stassen@stripped]
Sent: Monday, May 03, 2004 12:57 PM
To: Bob Ramsey
Subject: Re: urban myth?
Bob Ramsey wrote:
> Ah, but the ordering is not random. As your example has it, the
> are in the order that the entries were inserted into the table. There
> is an explanation for the order of the returned data.
Apparently not random, but not in the order inserted either. Consider:
create temporary table foo (num int(10));
insert into foo values (1), (2), (3), (4), (5);
select * from foo;
delete from foo where num = 3;
insert into foo values (6);
insert into foo values (3);
mysql> select * from foo;
| num |
| 1 |
| 2 |
| 6 |
| 4 |
| 5 |
| 3 |
6 rows in set (0.01 sec)
(Same example as before with the "delete...where num=6" removed.) Note
6 is where the 3 was originally, because the slot where the first 3 was
inserted/deleted was reused for the 6.
This trivial example yields results which are ordered neither by num nor
the order inserted. The lesson is clear: The *only* way to be sure your
rows are sorted in a particular way is to explicitly request it with an
ORDER BY clause, as several others have pointed out. This is really a
fundamental principle: It is the data in the row that matters, not how
where it is stored.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql