homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

PHP MYSQL and Primary Keys

 8:34 pm on Jan 20, 2005 (gmt 0)

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?


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?



 9:34 pm on Jan 20, 2005 (gmt 0)

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

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

- Ryan


 9:47 pm on Jan 20, 2005 (gmt 0)

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.


 3:44 pm on Jan 21, 2005 (gmt 0)

You are correct, jatar_k.


 3:49 pm on Jan 21, 2005 (gmt 0)

jatar_k and coopster

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

~ confused ~


 3:56 pm on Jan 21, 2005 (gmt 0)

255 they say...



 3:59 pm on Jan 21, 2005 (gmt 0)


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


 4:06 pm on Jan 21, 2005 (gmt 0)

Yes. Or whatever size you determine is necessary.


 4:17 pm on Jan 21, 2005 (gmt 0)

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 :)


 4:42 pm on Jan 21, 2005 (gmt 0)

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?


 4:58 pm on Jan 21, 2005 (gmt 0)

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

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved