disabling re-use of deleted records in Mysql

Question

I have a web application where I interact with a MySql database. When I delete a record, it seems that its slot is reused the next time I create a new record. Since each record has a sequential primary key, the next record will have a new key say 5, but the new record will be placed in the last emptied spot, say where record number 2 used to be. Is there a way to have new records added directly at the bottom of the list? Is there a way to do that with MySQL Query Browser perhaps?

This is an example of what I am describing:

1 userName1 password1
2 userName2 password2 --> next operation will delete this record
3 userName3 password3
4 userName4 password4

when record #2 is gone, next time I do a new add, record #5 takes up number 2 spot:

1 userName1 password1
5 userName5 password5
3 userName3 password3
4 userName4 password4

Is there a recommended way to instruct MySQL (perhaps at table creation) that new records should only be added at the bottom of the list? so, we would like to disable the automatic record-reuse.

Thanks in advance for your help Kindest regards

Answer 1

Simply add

ORDER BY id

to the end of your query (where "id" is the 1, 2, 3, 4, 5 column in your table)

Answer 2

You can create an "on insert" trigger which uses a sequence to set the ID (writing from memory according to Oracle, so you'll have to check if it's different in MySQL):

create sequence table_name_id_seq order nocache cycle maxvalue 99999999;

create or replace trigger table_name_id
before insert on table_name for each row
begin
  select table_name_id_seq.nextval
    into :new.id
    from dual
  ;
end;

Answer 3

InnoDB will handle that for you on some level. All InnoDB tables have some form of primary key which is clustered, and those are typically setup to handle inserts sequentially.

If you're prevented from using InnoDB, I believe you'll have to resort to changing source code to adapt how MyISAM works. I'm not particularly familiar with any other "normal" storage engine for MySQL, so I'm not sure if there's another alternative.

Answer 4

creating date field (date_added) and ORDER BY date_added would be better