MySQL Primary key gap

[ZeroError]

Alright, so I have a table with 26 entries in it (id, filename, caption) for my image randomiser (http://zeroerror.co.uk/community_img.php) which is working fine, technically, but I have a slight problem.

The primary key (id) is set to AUTO_INCREMENT and I have recently deleted a row, so it counts 1, 2, 3, 4, 5, 6, 8, 9, etc.

I'd like to know the easiest method of fixing this, which I can build into the deletion script I have written for the administration panel.

 

Any thoughts?

Thanks in advance.

[JonnoTheDev]

Why should this matter?

[ZeroError]

Ah yes, sorry, well the PHP script which finds a random filename from the table counts the number of rows then picks an id between 1 and the number of rows, meaning if I my last entry has an ID of 27, it will be left off, as the randomiser will only count 26 rows. It also looks untidy.

[oni-kun]

Ah yes, sorry, well the PHP script which finds a random filename from the table counts the number of rows then picks an id between 1 and the number of rows, meaning if I my last entry has an ID of 27, it will be left off, as the randomiser will only count 26 rows. It also looks untidy.

 

With your random selection, check if the ID exists before calling it? Seems very simple, as those deleted entries, nothing you can do with them.

[Alex]

This isn't really a problem with the ID skipping. If every time you deleted a row all the unique ids shifted down that would kind of defeat the purpose, wouldn't it? It wouldn't be a constant anymore. If this gap is giving you a problem then it's likely it's a design flaw in your program.

 

If you want to select a random record from a database you can use ORDER BY RAND() LIMIT 1. This doesn't scale well, so if you're dealing with a large database you might want to look into some of the alternatives, there's a sticky for this in the MySQL forum.

[JonnoTheDev]

This is poor script design, nothing to do with the database. A primary key is simply a unique identifier and not to be changed. It is used to construct relationships with other tables meaning if a PK where to change all associated records in union tables would have to be updated. If you have hundreds of tables with millions of records then can you imagine the level of additional queries that you have to implement into a system making it massively error prone.

[ZeroError]

OK, thank you.

My website isn't amazingly popular, I doubt that will cause much of a problem.

 

I'll check out that sticky, anyway.

[Mchl]

I have found an interesting solution here:

http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/

(section 6)

[ZeroError]

Thanks, Mchl, that saves me having problems when it gets bigger. Very nice article, too!

 

Problem solved, methinks.

[Mchl]

There's a green button on the left side at the bottom to mark topic as solved.