news <[EMAIL PROTECTED]> wrote on 10/05/2005 15:13:49:
> In article <[EMAIL PROTECTED]>,
> [EMAIL PROTECTED] writes:
>
> > Hi,
> > Have you forgotten what's a primary key ?
>
> > Using order by will sort data, and if it's already sorted, it willbe
sorted
> > again. Time, memory and maybe disk io.
>
> If MySQL really does that, I'd consider this a bug.
I agree. MySQL knows if the search order implicitly delivers the data in
the requested order, and skips the sort phase if so.
> > Using the marco example, i gaved a solution considering iy's what
> he wants. Till
> > now i don't know if it's ok or not.
>
> > if so, just add :
> > select * from temp order by Id LIMIT 3,4;
>
> > if no, the primary key index will give you the order.
>
> I dunno what you're talking about, but definitely not MySQL 4.1.11:
>
> 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. 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.
It is therefore *never* safe to assume any sort of ordering unless you
specify it.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
