Skipping ID Numbers?

[UrbanTwitch]

http://sodadome.com/members.php

If you look under ID. you will see a member called Adam who's ID is 17.

It is suppose to be 7. It skips. How do I fix it?

Also. I DID delete some accounts. Maybe thats it. If so how do I start all over to count from 1? Delete all accounts? Drop?

[Fou-Lu]

You are using an autokey, so the deletion of the records caused that. In order to repair it you need to defrag the table by either indicating its engine should be INNODB or by issuing an optimize statement on MySQL 4. This can only be done if you delete that final record.

If you don't need to display the id associated with the user and only use it for display than use a for loop for whatever language you're using. Count it up for the number of records and display that one instead.

[UrbanTwitch]

Can you show me how to defrag the table?

[Fou-Lu]

Sure. If you're using INNODB:

ALTER TABLE `tablename` ENGINE=INNODB;

I don't remember how to do this on MyISAM, so I'll take a look around to see if I can find anything for you. Remember that this simply trims off the overhead for the table, so if you're next autokey is 15 and you're last entry is 9, it will reset the next autokey to 10. It won't reuse keys that have been deleted between sets of numbers.
Generally speaking though, the id thats in use is irrelevant and does not matter if it follows a smooth sequence or not - its in internal thing that makes programming easier for us. In other words, its no big deal if there are gaps in ids, and its often why sites don't display ids for you to see (it does kind of look ugly).

[Brandoe85]

Exactly - it shouldn't matter, take a look at guelphdad (http://www.codingforums.com/member.php?u=32996)'s article:
http://guelphdad.wefixtech.co.uk/sqlhelp/gaps.shtml

[UrbanTwitch]

I am using MyISAM

[UrbanTwitch]

So.... yeah... anyone know?