Primary Key, mediumint(8) Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
foeggy
Forum Newbie
Posts: 5
Joined: Wed Aug 04, 2004 12:49 am

Primary Key, mediumint(8) Question

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
foeggy
Forum Newbie
Posts: 5
Joined: Wed Aug 04, 2004 12:49 am

Post 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... :roll:
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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... :roll:
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...
foeggy
Forum Newbie
Posts: 5
Joined: Wed Aug 04, 2004 12:49 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.. :D
foeggy
Forum Newbie
Posts: 5
Joined: Wed Aug 04, 2004 12:49 am

Post 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, :lol: .
Post Reply