delete/insert id problem

[Anon]

I have a table in my mysql database with the following structure:

================================================
CREATE TABLE wie (
id int(10) NOT NULL auto_increment,
user varchar(20) NOT NULL default '',
PRIMARY KEY (id),
KEY user (user)

) TYPE=MyISAM;

Selecting, deleting and inserting data are no problem. But i discovered something strange:

When i delete a row from this table, the row is removed. After some time i insert a new row, but this row does not get the id from the just deleted row. The sql queries are:

"delete from wie where id='$id'"
and
"insert into wie (user) values ('$user')"

I did expect that the new inserted row would take the id from the deleted row.

What am i'm doing wrong? Is it maybe possible to reset the number of "id" to zero again, cause it increases with every insert.

Best regards,
Twan

[soulreaver]

the auto_increment you have set will always increase the number of the id column.

It is possible to to reset the number to 0 by emptying the DB or you can specify an ID number to use. I don't remember the syntax off the top of my head so have a look at the MySQL manual. It is in there.

Neil

[Anon]

Thanks for your quick reply!

My situation is as followed:
Every second 5 rows are added to the table, at the same time 4 or 5 rows are deleted.

This leads to very high id numbers. The limit "int(10)" will be reached soon.

I thought that mysql would insert new records at the place of a deleted record. This is not true in my situation. Can this be done?

[[deleted]]

Never limit the size of your int (makes no sense).

About 'reaching the limit soon':
An unsigned INT can hold max 4294967295.
there are 246060 seconds in a day, and 5 records per second = 52460*60 = 43200 records per day. 4294967295 / 43200 = 99420 days, which is 272 years.

Another small point to note is that adding 86400 records per day means you'll reach 2 million records in under 24 days. Even at 100 bytes per row you're talking about a datafile of 2M*100bytes = 200MB.
Your inserts may start taking longer than one second and you cannot even do 5 inserts per second.

In a year, you'll have 15 times that, or 3 Gigabytes. But that time you'll need at least 20 seconds just to select one record.

[Anon]

I thought limiting the size of my int was a wise idea to increase the speed, but i'll remove the limit off the int.

Thanks for pointing me the right way, with the calculations!

About the size of the database: rows will only stay less then 15 minutes in the database. After 15 minutes they will be removed by a cronjob. This means my table is never greater then about 1 - 5 mb 🙂

[[deleted]]

saving diskspace can be wise and important, but one char per row is not a big deal for size, but does scale down the max of the content by a factor of ten.

In your particular setup, where you never have many records, but do have many inserts, you might want to look at postgreSQL. PostgreSQL supports cycling or serials. That means, when the auto-increment (serial) has reached it's limit, it will automatically start re-using unused values. Just make sure your data is consistent, or all hell will break loose.