Page 1 of 1
Primary Key, mediumint(8) Question
Posted: Wed Aug 04, 2004 9:17 pm
by foeggy
What's up!
I had a quick question for anyone out there since I always get stuck searching for seemingly simple solutions.
My primary_key column is of type mediumint and a length of 8. I have a script that runs every twenty minutes and stores more information into the database. After about a week of running the primary_key's are already up to 17,000. I was wondering if this is going to pose a problem in the near future as the column's value gets higher and higher.
Thanks,
geoff
Posted: Wed Aug 04, 2004 9:47 pm
by feyd
yeah.. you'll run out of space very quickly.. unless you don't mind recycling, you'll have to switch types to bigint probably.. that should last you a while longer..
Posted: Wed Aug 04, 2004 9:52 pm
by foeggy
What exactly do you mean by recylcing?
An idea I had to solve it would be to every week, reset the primary_key column. Old records are deleted from the database as well and the primary_key column really serves no purpose in distinguishing distinct records, apart from each other...

Posted: Thu Aug 05, 2004 7:59 am
by AGISB
I just had the same problem. I have learned that you don't need a primary key as long as you don't really have to access any particular single row. In my case I am logging logins in a logfile (nice sentence

) and I can just look for anything that is user or ip etc.
If you need a primary I came up with the following. you could use a mircotime() as primary. This will give you a unix timestamp plus the microseconds of a second.
This is the original post:
viewtopic.php?t=24591
Posted: Thu Aug 05, 2004 8:01 am
by JAM
foeggy wrote:What exactly do you mean by recylcing?
An idea I had to solve it would be to every week, reset the primary_key column. Old records are deleted from the database as well and the primary_key column really serves no purpose in distinguishing distinct records, apart from each other...

You answered your own question.

Recyling, Recycle Bin, Trash. What do you tras? Old things you have no use of anymore...
As you menation that the primary_key field does not serve any purpose, you could shange it do a non primary_key. When that later reaches the max of the mediumint, you simply restart from #1 again. First "delete from table where id = 1" then "insert ..." you data.
There is also the use of timestamp. If inserting X rows of data, delete the same amount of rows from the beginning of the table, using the timestamp to ORDER BY. Then on the other hand, this means that you don't need a id-field at all so that thought was perhaps stupid...
Just thoughts, just thoughts...
Posted: Thu Aug 05, 2004 11:54 am
by foeggy
I see what you are all saying but there are instances in the script where I need a primary key. The timestamp will not work because it stores 10-50 records at one time, so they'd all have the same time stamp.
I'll probably just reset the primary keys somehow. I just wanted to get the idea out there in case anyone else had a similar problem.
Thanks!
geoff
Posted: Thu Aug 05, 2004 12:03 pm
by feyd
I'd still suggest changing it to BIGINT.. you'd then have a minimum of 9,223,372,036,854,775,808 combinations (unless you also use UNSIGNED, then it's 18,446,744,073,709,551,616) keys before you run out..

Posted: Thu Aug 05, 2004 12:56 pm
by foeggy
Hahaha, alright thanks. I think that'll give me a
little time to figure out what I want to do. Oh, and it is unsigned,

.