Welcome to WebmasterWorld Guest from 54.167.252.62

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

PHP MYSQL and Primary Keys

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

10+ Year Member



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?

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

10+ Year Member



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)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



You are correct, jatar_k.
3:49 pm on Jan 21, 2005 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



255 they say...

[dev.mysql.com...]

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

WebmasterWorld Senior Member 10+ Year Member



coopster

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

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

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Yes. Or whatever size you determine is necessary.
4:17 pm on Jan 21, 2005 (gmt 0)

10+ Year Member




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)

10+ Year Member



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)

10+ Year Member



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
 

Featured Threads

Hot Threads This Week

Hot Threads This Month