PHP MYSQL and Primary Keys

[tongpo]

Say I have an ID field in my database that is a primary key and an UNSIGNED AUTO INCREMENTING TINYINT.

It stores range of 0-255. Ok I add a few items to the database, say 240. At a later date I delete a few records, say rows 33, 44, 55, 66, 77, and 240.

So the last record in the database now has an ID of 239, I add another record and it gets assigned an ID of 241?. I then add a few more and it finally reaches ID 255. Now all the ones I deleted previously do not exist. So I try to add another record, will it just get added and assigned the ID of a record that doesent exist such as 33, 44, 55, 66, 77 or 240? If not is there a way around this?

I am pretty new to MySQL and I dont know the answer to this but I know that it could prove to be pretty inefficient if an ID field can hold 255 records but only holds 240. Do you understand what I mean and can anyone answer my question?

Thanks

P.S Another quick question: Is there a way that database query results can be sorted randomly? So two exact queries will produce the results in a different order?

[Nutter]

I'm not sure about the first part...

But, you can do ORDER BY RAND() and it will sort randomly.

- Ryan

[jatar_k]

I would make your tinyint bigger, i do not believe mysql back fills the ones that are gone and I believe this should be the appropriate behaviour.

[coopster]

You are correct, jatar_k.

[jamie]

jatar_k and coopster

i always thought tinyint could only go as far as 256?

~ confused ~

[coopster]

255 they say...

[dev.mysql.com...]

[jamie]

coopster

so "I would make your tinyint bigger" means make it an int instead?

[coopster]

Yes. Or whatever size you determine is necessary.

[dmmh]

i do not believe mysql back fills the ones that are gone and I believe this should be the appropriate behaviour.

thank god for that yes :)

[tongpo]

hmmmm....Im sure there must be a way around this. What happens to tables/databases that regulary get updated changed etc. Such as rows being deleted and new ones inserted etc.

For instance I have a table with an auto_incrementing primary key as in my previous post. The table gets added to and rows deleted over a period of time. But MySQL inserts only up to ID number 255 as its data type is TINYINT, but the table has changed a lot and a lot of rows have been deleted so really the table only holds 20 records but it wont allow me to add any more as MySQL is trying to add record NO. 256. Ok change the data type you say, that will sort the problem out and yes it will.........but its ineficient. It consumes more disk space than is necessary. A TINY INT - 1 byte, a SMALLINT - 2 bytes. a MEDIUMINT - 3 bytes. an INT - 4 bytes and BIGINT - 8 bytes. So why use 8 bytes of space 4 will do?

But lets say I wasnt concerned about disk space. I changed the type to SMALLINT so I can now add more records. OK but over time it constantly changes with new records being added and deleted etc and It reachs its limit of (SMALLINT 65535). But in reality it holds no where near this number of rows but same scenario as before and I change data type. And on and on and on......until finally one day (few years on) It reaches the maximum amount it can hold BIGINT but I cant change type to add more rows so what then?

[dmmh]

you could always export the table and import it again, empty keys will be empty and the fragmented table will have all rows at the beginning