Re: PHP + MySQL - Load last inserts

On Mon, Mar 30, 2009 at 5:10 PM, Chris <dmagick@gmail.com> wrote: > haliphax wrote: >> >> On Mon, Mar 30, 2009 at 9:13 AM, Sebastian Muszytowski >> <s.muszytowski@googlemail.com> 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