Eko Budiharto <[EMAIL PROTECTED]> wrote on 06/27/2005 12:28:27 PM: > >Are you actually saying that you have a database with more than 1.8e+19
> >records in it? I don't think you do. I think you are combining several > >pieces of information into something that looks like a number and it's > >exceeding the storage limits of even BIGINT. > > >What you have is actually a "good idea" but you are physically limited by > >the capacity of the column types available. In this case if you cannot > >create all of your key values so that they look like numbers smaller than > >18446744073709551615, it can't fit into a BIGINT UNSIGNED column. > > What I have in here, I have a table that use integer(BIGINT) for > index/ID and auto increment. > CREATE TABLE server ( > ID bigint(20) auto_increment > , name varchar(25) not null > , ip int unsigned > , ... (any other fields you could define to describe this server) > , PRIMARY KEY (ID) > , UNIQUE(name) > ) > > I use BIGINT because it has the capability auto_increment for index > of records instead of using character. What I have in here, I am > accessing my DB with ID, name. > Any integer column can auto_increment. Just be careful you don't add enough records to exceed the storage limit of your auto_increment column. For example if you use a TINYINT column to store your ID value, you could add at most 127 new records before your auto_increment would start failing. I recommend INT as a storage type as it is not very often that you wind up with 2147483647 records in a table and it only uses half as many bytes (4) to store the value as BIGINT(8). If you had 2000000 rows and you used a BIGINT as your ID value, the space needed to store the data from just that one column would be 8*2000000 = 16000000 bytes but if you had used a regular INT as your ID column it would have only used 4*2000000 = 8000000 bytes (about 8MB less disk space) That is a lot less data you need to move into memory whenever you need the PK index for that table. That's also a lot less data to read through when you search an index (Data bus speeds are fast but not infinitely so. It still takes some time to read a page of memory.). By the way, it is appropriate to respond to the list. Just in case I wasn't available to respond to you, someone else could answer your questions. It also gives the list a chance to learn from any answers you get. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
