On Mon, Mar 30, 2009 at 5:10 PM, Chris <dmagick@xxxxxxxxx> wrote:
> haliphax wrote:
>>
>> On Mon, Mar 30, 2009 at 9:13 AM, Sebastian Muszytowski
>> <s.muszytowski@xxxxxxxxxxxxxx> wrote:
>>>
>>> haliphax schrieb:
>>>>>
>>>>> [..cut...]
>>>>
>>>> Except when your primary key value rolls over, or fills a gap between
>>>> two other rows that was left when a row was deleted/moved/etc... there
>>>> has got to be a better way than grabbing rows in descending order
>>>> based on the auto_increment value.
>>>>
>>>> Are you doing the inserts one at a time? If so, why not just use code
>>>> to remember what you put in the DB?
>>>>
>>> I do the inserts one at a time, i could use code to remember but i think
>>> it
>>> would be very slow when i have too much users.
>>>
>>> The second thing that aware me from doing this is, why use/build
>>> ressources
>>> when you could use others? i think why should i use and add another
>>> system?
>>> I work with mysql already and when php+mysql have the function i need
>>> it's
>>> better and i don't waste ressources :)
>>
>> Insert 100 records. Delete 50 of them at random. Now do your "grab the
>> last few records and sort them in descending order" trick and see
>> where it gets you.
>
> Mysql does not re-use id's (nor does any other db as far as I'm aware, and
> nor should it). I don't know where you got that idea from.
>
> mysql> create table test (id int auto_increment not null primary key, name
> text);
> Query OK, 0 rows affected (0.12 sec)
>
> mysql> insert into test(name) values
> ('one'),('two'),('three'),('four'),('five'),('six'),('seven'),('eight'),('nine'),('ten');
> Query OK, 10 rows affected (0.00 sec)
> Records: 10 Duplicates: 0 Warnings: 0
>
> mysql> select * from test;
> +----+-------+
> | id | name |
> +----+-------+
> | 1 | one |
> | 2 | two |
> | 3 | three |
> | 4 | four |
> | 5 | five |
> | 6 | six |
> | 7 | seven |
> | 8 | eight |
> | 9 | nine |
> | 10 | ten |
> +----+-------+
> 10 rows in set (0.00 sec)
>
> mysql> delete from test where id in (1,3,7);
> Query OK, 3 rows affected (0.00 sec)
>
> mysql> insert into test(name) values('eleven');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from test;
> +----+--------+
> | id | name |
> +----+--------+
> | 2 | two |
> | 4 | four |
> | 5 | five |
> | 6 | six |
> | 11 | eleven |
> | 8 | eight |
> | 9 | nine |
> | 10 | ten |
> +----+--------+
> 8 rows in set (0.00 sec)
>
> The physical order of the rows changed (as you can see) but it does not
> re-use the id's that were deleted.
>
> Ordering by the id will get you the last bunch inserted.
>
> If you're using innodb you'll have to be aware it's transaction specific, if
> you're using myisam it will be system wide.
I'll have to check my DBs then, because the exact situation I
described happened to me a few years back when I was first getting
into the DB scene. Regardless, I'm fairly certain the MSSQL box we
have at work behaves this way.
--
// Todd
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
