auto increment running out of keys

[sem_db]

I understand why people want to adjust their keys, because after deleting a records and adding new ones, eventually you will run out of unique keys. Of course, you have at least 4 billion unique keys with an unsigned int. My solution is, use a PHP function like uniqid(some parameter) to develop a unique id from the millisecond timer on your machine, then insert that into the table.

My question is.. does anyone know if you can still access the last inserted key value using the mysql_insert_id() function if the column type is not int auto increment but something like int(10)?

eg.
original data set
1
2
3

delete 2
auto increment
1
3
4

delete 3
auto increment
1
4
5

.. i'm running out of keys!

[the_Igel]

In case you have loads of records you will run out of bigint, either it was generated by auyo_increment or whatever. But I think bigint is big enough.

And yes, you can't retrieve mysql_insert_id in case it's not an auto_increment. So you will have to make one more query:

1. insert smth into smth;
2. select id from somewhere order by time desc limit 1;

but (if you're afraid of running out of bigint, you should have loads of requests 🙂) if another user inserts a record between execution of these two queries, your db will be messed up.

[sem_db]

Great, thanks!
So the conclusion is .. use auto increment, unless you can perform some sort of table locking.

ps.
What is limit 1? I assume you have a colum in the table which holds a millisecond time value which you read using the "limit 1" statement.

[csousley]

limit X is just pulling the first X matchs found to the query. Like so

select * from table where name = 'sam' limit 2

Now say there are 5 matchs for sam in the name col. The query will only bring back the first 2 found.

Make Sence?

[the_Igel]

SELECT something FROM somewhere ORDER BY time_column LIMIT 1 is how you retrieve last record from the table. Or you can select LIMIT 10, 20 - then you will get records from 10th to 29th.

[[deleted]]

"So the conclusion is .. use auto increment, unless you can perform some sort of table locking. "

I'd say the conclusion is to always use auto_increment, fullstop. It's pahntom problem. You will run out sometime, but when is sometime?:

An unsigned bigint ranges from 0 to 18446744073709551615.

One year has 606024*365=22896000 seconds, which means you can continue inserting one record per second for
18446744073709551615 / 22896000 = 805675405036 years. That means you can continue to insert 1.000.000.000 records per second for 805 years(!)

[the_Igel]

I can imagine a reason for not using auto_increment: in case one wants to use this field not only as ID, but also as some data field: encoding records hierarchy in these numbers, or something of the like. Kind of ultimate hard-disk space saving techniques. But of course it's for brain-damaged people, we normal guys would never do that much trouble for ourselves 🙂 ID for identification and nothing more, auto_increment forever!..

[[deleted]]

Very true. There are always people (sick, twisted people) who feel they have to use an alphanumeric PK value, and generate it by hand by locking the table etc... ugh...

[the_Igel]

Originally posted by vincente
an alphanumeric PK value

What is PK?

[[deleted]]

PK = Primary Key.
and likewise, FK = Foreign Key

[the_Igel]

Oh, I see.

[sem_db]

okeeee dokee, I will NEVER use anything other than auto_increment!