auto_incremenent special functionality

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
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

auto_incremenent special functionality

Post by d3ad1ysp0rk »

Is there a way for an auto_increment to check the LOWEST available id? We have an items table which stores hundreds of thousands of records, but users drop items a LOT, so there are TONS of unused ids, even though the ids are getting huge..

Thanks!
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

We've talked about this before... use the search. But seriously, you don't really need it, auto_increment should be good enough for whatever it is that you are doing.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Just make sure your ID bandwidth is large enough.. i.e. use a type that supports massive numbers and set the field to unsigned too.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

Oren wrote:We've talked about this before... use the search. But seriously, you don't really need it, auto_increment should be good enough for whatever it is that you are doing.
Table contains 2,106,403 rows. The current id is 548,772,067. Just seems like a waste.

Also, my search for "auto_increment" brought up scarcely anything related to what I was asking.. sorry for posting in the forum.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Think about this logically... auto_increment. What does that imply? That the field will increment itself as the data grows. The fact that there are gaps in your numbering sequence of your IDs means nothing. Nor should it. The ID field, at the data level, is merely an identifier of the row.

Yes, it can be used as an index for searching (and in fact it probably should), but the gaps in the sequences mean nothing.

As for searching, you may also try searching for the term 'identity column' which is a term used often by the likes of Oracle, Sybase and MS SQL Server to describe a primary key, auto incrementing field.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

d3ad1ysp0rk wrote:Table contains 2,106,403 rows. The current id is 548,772,067. Just seems like a waste.
A waste of what? If you're storing the values as INT in the database then 1 takes up the same space as 548,772,067 ... it'll always be 4 bytes on disk regardless of what it actually is.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Don't forget integers have a maximum number.

You could probably run a sub-select to find the next lowest number on insert, but it will hinder your applications performance.
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

Oren wrote:We've talked about this before... use the search. But seriously, you don't really need it, auto_increment should be good enough for whatever it is that you are doing.
I may be trying to put out a fire with gasoline, but what if it isn't good enough? I'd be interested in an answer to this just from an acedemic standpoint.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Can you give an example of when it wouldn't be?
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

Everah wrote:Can you give an example of when it wouldn't be?
No (I can't see anything needing >2^32 records), but neither can you prove that there will never exist a situation where it's not. That's why said I was interested from an acedemic standpoint :D
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

As everah and others have said (over and over again), it doesn't make any difference, as long as the numbers are unique! If you start playing games with auto_increment, you risk screwing up your database by inadvertently creating a duplicate key. Some people always want to use elements for purposes other than for which they are intended. That's not a good idea.
Post Reply