On Wednesday 18 July 2001 13:32, you wrote:
> I recently got into a discussion with a colleague that was interesing. He
> is a database warehouse guy, so I assume he knows more than I do on this
> subject, but he was unable to explain things to me very well :)
>
> The question is how does mysql organize data between the physical table,
> the select statement and auto_increment?
In the physical table, lots of times, if you delete a record, it will not be
either the first or last (physically), in fact, the chances are very small.
Rather than re-order the whole file each time you delete a record, you just
mark that space as open, a "hole" if you will. Next time you insert a record
small enough to fit into that "hole" you just stick it there, rather than at
the end. Then, you can regularly "de-fragment" the files, by closing all the
"holes" and compacting all of the records. Therefore, if you insert a record
into a "hole" the physical order is different than the order of the order by
columns. The ANSI specs don't say anything about physical storage.
> As an example, if I insert one new row per second into a table with
> auto_increment not null set, I had thought that when I make a select all
> (not ordered) that the resulting data set would be in SOME kind of order.
>
> In this paradigm, my colleague says that there are no guarantees that the
> records will be retrieved in any kind of order, and may in fact be random.
> How does this work?
The aboove example explaines this, if you have records out of order, and
select without ordering, it's silly to sort b/c that's extra work.
Therefore, the select will just get the data in the order that htey're on the
disk (linear scan) and they will be out of order, as explained above.
>
> He also states that auto_increment does not guarantee consistent
> increments. All it does is guarantee increasing increments. So in fact
> what he says is that if I insert 5 new records into an empty table with id
> as
> auto_increment, the corresponding ids may be 1,5,7,23,24... or
> 10,19,21,30,43, etc...
>
See the ANSI Specs, they define this...
> I had thought that auto_increment incremented by 1, assuming the paradigm
> described above, and no deletes from the table.
>
> His reasoning for both of these examples is that the data may be written to
> any physical location, and therefore retrieved in any order. I am a bit
> suspicious of this, as I had thought that the new data in a table was
> appended to the table (again using the paradigm above as the case).
>
> Any thoughts on this stuff?
>
> Thanks,
> Bryan
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]> Trouble
> unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
