MySQL ID?

[The Hobo]

I have the ID field setup on my table as auto_increment, primary key, and unique. However, when I add new items to the database, the ID is not always the next available number. Like when I delete some records (say 4 through 6) instead of starting at 5, it starts at 7. How can I prevent this?

[The Hobo]

The way I'm doing it now is specifying the ID myself by getting the ID of the previous item.

[chrisjk]

that's how autonumber works on most (if not all?) rdms...it NEVER uses the same number twice regardless of whether the number is in use or not

[The Hobo]

So should I just shut autonumber off and find out the next number through code? I just don't want huge gaps in my ID numbers, 1, 2, 8, 14, 15, 16, 22. You know?

[chrisjk]

you will get gaps even with your current method

Say you had records

1
2
3
4
5

then you deleted 2 and 3, you'd now have

1
4
5

when you insert a new record, the id will be 6 if you get the ID of the last record and add 1 still, the same as if you just let auto_increment take care of it.

If you only ever delete records at the end of the table, then your method would work so yes you should turn auto_increment off, but it won't make any difference if you delete records in the middle of the table.

[The Hobo]

Good point. But it would still make the gaps smaller. Right now, the next autoindex is 23, and there's only 11 records in the table.

Thanks for your help.

[scoutt]

just go in and re-number the id number. if there is only a 11 then it should be pretty fast.

if you turn off autoincrement and get the number from the last id yourself, how is that going to make the gaps smaller if you are still deleting form the middle? sorry just curious.

[The Hobo]

Originally posted by scoutt
just go in and re-number the id number. if there is only a 11 then it should be pretty fast.

I expect that database to grow in time.

Originally posted by scoutt
if you turn off autoincrement and get the number from the last id yourself, how is that going to make the gaps smaller if you are still deleting form the middle? sorry just curious.

Because currently, the next autoindex is like 23, while my code to get the last id will give me 10. Add one, the next number is 11. See what I'm saying? The autoindex is already way up because of how many I've deleted.

[scoutt]

I don't think it works that way. you will get conflicts on that number already being used. if you get total rows, say 11, then you add one to it, mysql is going to say that that id is already in use (duplicate). you will start having problems when you DB gets bigger. say you have 500 rows and you delete # 250 so now you have 499, but you have an id of 500. if you get the total rows then add one it will give it an id of 500. see it will give you an error since there is already an id. even if you used last_ID or whatever it is, it will give you the last one, order or not.

if I am way off base here let me know, you might have to explain to me like I'm a 3rd grader,

[The Hobo]

Ah, but I'm not using the number of rows, I'm doing this:

$q = "SELECT id FROM newsitems ORDER by id DESC LIMIT 1";
$id = mysql_fetch_array(mysql_query($q)) or die(mysql_error());
$id++; 

Or something like that.

So it's finding the last record in the table, getting it's ID and then incrementing it for the record I'm about to add.

Do you still think it will cause a problem later on?

[scoutt]

that shouldn't no, but you are still not filing the wholes from the ones you delete. that is like an auto-increment isn't it?

[The Hobo]

No, it's not filling the holes (if you could think of a way to do that, that'd be awesome), but it is preventing a good portion of them. Like I said, the next autoindex is 23, my code will give 11. So it's like autoincrement, but smarter. It will use the numbers twice if the number no longer exists (only at the end of the number set).

But no. Unfortunatly, it wont fill holes.

[scoutt]

if physically in your table you have id=23 then that code you showed will return id=24. how will it get 11

[The Hobo]

The next AutoIndex is 23. Meaning that if I let autoincrement decide the ID, it would put in 23. But since the ID of the last record in the table is 10, my code gets that, increments it, and gives the new record the ID 11. In this process, AutoIndex will increment to 24. Even though my code overrides it, it still increases.

I wish I could show you. It'd all make sense fast.

[The Hobo]

This is in phpMyAdmin. The next autoindex is at 18, but I only have 4 rows (the id of the last row happens to be 4 to, so this one doesn't have holes yet).

If I were to let autoincrement chose the ID, I would have 1, 2, 3, 4, 18...but with my code, I get 1, 2, 3, 4, 5.

[scoutt]

no I see what you are saying. its funny because my test machine at home that has apache and php and mysql on it doesn't do that. if I have a last id of 10 and I delete it, the table will still insert #10, but on my site if I did that it would not increment the same.

so, if your table has an ID of 10, how is autoindex going to return 23? if the last id is 10 it should autoindex 11 not 23. if that is the last row in the table. but if you have anything higher than that higher up in the table it will return 23 or whatever.

1
3
23
5

even though the last id is 5 the next autoindex is 24. make sure you script doesn't do that as it will return 23 instead of 5

[scoutt]

what version do you have? mine doesn't have that one and I have 2.2.4

if that is the case then mysql is remembering the last id that was in there but had been deleted. my site doesn't do that. if I deleted #4then the next one is #4 not 5. very strange

[The Hobo]

According to phpMyAdmin, I'm running MySQL 3.23.45

[scoutt]

no the version of phpmyAdmin

[The Hobo]

Oh. I'm running phpMyAdmin 2.2.2

[The Hobo]

I'll contact my host and see if they can update it to 2.2.6 (since they have one setup for every account)

[scoutt]

that won't matter. it is mysql that is messin gup. I was jsut curious as to where you got the screen shot from since it is not on my version phpMyAdmin.

[The Hobo]

It's in 2.2.6 as well. When you click on the table to view it's properties. On the right side.

[scoutt]

well I wonder why they skipped some version and didn't put that in there.
I will have to download that version then. thanks

[The Hobo]

So do you think they way I'm going about it is best?

Without a loop, would there be anyway to tell where the first hole is?

1, 2, 3, _, 5

So I can insert a record where the blanks are?

[scoutt]

as far as I know tha tis a mysql thing and you can't control where it goes. mysql says it goes where it goes I guess.

[The Hobo]

You're not understanding nor answering my questions. But thanks anyways.

[scoutt]

I thougth I did. you can't insert data in a row that has been deleted or is not there. the way you are going, that is up to you. personally I would leave it at auto-increment. but to each his own I guess.

I have another site that has something like what you are doing. somebody can insert articles and it goes in the database. that person can have it set to 1 week up to 3 months. after that time it gets deleted on it's own. it checks everytime the page is loaded and if the dates match it gets deleted. now the first one can get deleted in a week and the last one will take a month. my point is that I don't get holes like that, and it is set to auto-increment.

if your database is going to get real big, then why consider deleteing them? how can it get big if you delete them? if it gets out of hand you can dump it and re number it then load it back. that will take all of 10 minutes. and the site will never know the difference.

that is my opinion and not to be considered your only option.

[The Hobo]

I don't know what you're talking about. Just nevermind.

[scoutt]

Originally posted by The Hobo
This is in phpMyAdmin. The next autoindex is at 18, but I only have 4 rows (the id of the last row happens to be 4 to, so this one doesn't have holes yet).

If I were to let autoincrement chose the ID, I would have 1, 2, 3, 4, 18...but with my code, I get 1, 2, 3, 4, 5.

I just installed 2.2.6 of phpMyAdmin and that picture you showed is not in 2.2.6. I clicked on properties of a table and it is no where to be found.

[chrisjk]

that little ditty has been in every version of phpmyadmin since 2.2.3 I think, at least that is the first one I got

It's in the first section to the right of the indices under the details of all the fields in the table

[The Hobo]

you can't insert data in a row that has been deleted or is not there

Why not? The numbers aren't being used anymore. Seems to work perfectly fine to me. Just developing a method to find that missing number is what I can't do (without some kind of big loop)

my point is that I don't get holes like that, and it is set to auto-increment.

The point is, that mine does.

if your database is going to get real big, then why consider deleteing them? how can it get big if you delete them?

Because my site contains snippets and tutorials that users can submit. If a user submits some vulgar thing or something malicous, I need to delete it. Also if things become outdated, etc.

[scoutt]

Originally posted by chrisjk
that little ditty has been in every version of phpmyadmin since 2.2.3 I think, at least that is the first one I got

It's in the first section to the right of the indices under the details of all the fields in the table

well, I can't find it in 2.2.4 and 2.2.6.

Why not? The numbers aren't being used anymore. Seems to work perfectly fine to me.

you have it so it inserts the next id in a deleted row? so if you have
1
2
4

you can stick 3 in right after 2? you are a god. I don't know why I even bother.

[The Hobo]

Originally posted by scoutt

you have it so it inserts the next id in a deleted row? so if you have
1
2
4

you can stick 3 in right after 2? you are a god. I don't know why I even bother.

What the hell's your problem? Instead of being an ass about it, why don't you explain why it's not possible?

[scoutt]

why don't you explain to me how you plan on inserting an id in a deleted row.

since I am an ass.

so if you have

1
2
3
5

how do you plan on how you inserting 4 right after 3

[The Hobo]

INSERT INTO tablename (id,other,stuff) VALUES (4,'something','something')

Now why don't you, as I already asked, explain to me why that isn't possible? Or do we need to go back and forth for a few more posts before you get around to telling me?

I'm not saying you're wrong. I'm asking you to tell me why it's not possible. Do you understand?

[scoutt]

sure. how are you going to tell that there is no #4? you will have to hard code it and then even if you get it to go it will go to the end of all rows, not after 3. I will run a test on a table that is setup like this and see if it does. I'll get back to you in a few, or monday

[The Hobo]

Originally posted by scoutt
sure. how are you going to tell that there is no #4?

That's what I was asking you. If there was a way to do it without looping through and finding the first hole. Maybe I could create a table that would store the next available ID for all the other tables. When an item is deleted, it will specify that as the next number. When one is added in it's place, it'll get the ID after the next. Sorry if this is too confusing.

I know it'll still put it at the end, but then I wont have links like:

http://site.com/show.php?id=1
http://site.com/show.php?id=2
http://site.com/show.php?id=4
http://site.com/show.php?id=8
http://site.com/show.php?id=12

I guess it's not really a big deal, but I'm picky.

[scoutt]

I see what you are saying and sorry if I thought different. right off hand I don't know of anyway to do what you want. not saying there might be way, I just don't know.

how do you plan on getting the correct row if you don't specify an id in the url? specifing and id is better then the articles name or something.

[The Hobo]

I meant that I don't want spaces between my numbers, 1, 2, 4, 8, 12, but 1, 2, 3, 5 instead. I'll give what I said a try:

If an item is deleted from the table, the deleted ID number is saved in another table so it can be used again. Once it is used, it will get the last id + 1, like I said above. That way I can always know what the ID should be.

If more than one item is deleted, I'll add it as well, so I'll have:

3,14,12

and once they are all used up, just go to the last id + 1.

It's hard to explain, but I think it'll work.