Optimizing & Squeezing IDs

[drath]

I really had no idea what to call this...  hopefully it works. These two questions are more logical than technical; however, feel free to put code in too.

 

The first questions is about Primary IDs (keys) - the first column in my database which stores a number. My issue is the database has grown quite huge... we are getting up to 6 numbers. This normally couldn't be helped... except that many, many items have been deleted; however, with the way the auto-increment works, it goes after the last number instead of the next available. Is there an easy way to compress this down? I was generally just thinking of making a loop that would just assign new IDs in order; however, would that even work, or would it just keep adding new large numbers based on the auto-increment value?

 

The second question is about inserting new rows in between IDs that already exist. I wish I could insert a row with the same ID as something already in the database and it would just shift everything automatically. Unfortunately, I don't think that's possible. What is the best way to do this? For example, I want to insert:

 

3 orange

 

Into:

 

1 apple

2 banana

3 peach

4 grapes

5 cherry

6 purplezebra

 

Thanks!

 

[s0c0]

Have you considered using an INT(10) or even a BIGINT(19).  Also ensure your integer values are unsigned as this gives more positive integers to work with, but disallows negative integers.

 

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

 

I'm not sure about recycling IDs.  It depends on a lot of things, I wouldn't do it, but I'll allow someone else on here to comment on that.

[roopurt18]

If you did want to compress the keys yourself (via script or program), you'd probably want to use something like "ON UPDATE CASCADE."

 

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

 

I would explore alternative solutions though.

[Mchl]

Recycling IDs, or shifting them around is no kind of 'optimisation' and is not a good idea at all. Just let the auto increment machanism do it's job, because it does it well. By recycling, 'squeezing' or otherwise messing by hand with these ids, you're likely to corrupt referential integrity of your data sooner or later.

 

You say you're getting into 6 digit numbers (if I understood correctly). It's not that much. INT UNSIGNED can store numbers p to 4 billion so 4000 times more. How soon you're going to hit that limit? And even if you do, you have still BIGINT with 4 billion times larger capacity than INT. If your database ever grows big enough to require BIGINT primary keys, you'll probably be a rich man by then and will be able to afford a database engineer to deal with such worries for you

 

Other thing is, you shouldn't mix up primary key with record ordering. Primary key is for maintaining referential integrity. If you need your rows to be ordered in particular order (like to put orange between the banana and the peach) you should use a separate column for that. Shifting primary keys for that is 1. dangerous for referential integrity, 2. will be slow (imagine updating several thousands or millions of rows).

[fenway]

Yeah, never do this.

[schilly]

Don't bother. If you can cap out an unsigned BIGINT, I'd be very impressed.

[drath]

Okay, as I understand it, compressing the IDs is a worthless exercise... that's fine; however, in regards to my second question. I am using IDs as a sort method for new/old and I really need to shift some around.

[Mchl]

No you don't. You need to add another column that you will use for sorting/ordering.

[drath]

No you don't. You need to add another column that you will use for sorting/ordering.

 

Whether I was using IDs of fakeIDs as a column, I still need a way to shift them around, assuming I am using a numerical value. It wouldn't be a problem if there was 10 rows; however, with 10,000 it becomes more of an issue.

[fenway]

Shift what around?