Welcome to WebmasterWorld Guest from 220.127.116.11
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?
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?