Re: Auto_increment field size (was: Howto make a double LEFT JOIN)

----- Original Message ----- From: "Bas Jobsen" <bas@startpunt.cc> To: "Sheridan Saint-Michel" <webmaster@foxjet.com>; <php-db@lists.php.net> Sent: Friday, October 05, 2001 9:50 AM Subject: Re: [PHP-DB] Howto make a double LEFT JOIN <snip> > > ACK! Don't do this. You do know that an unsigned int (using a signed int > > in an auto_increment field is pointless anyway) has a max value of > > 4294967295, right? To put this into perspective... if you had one million > > customers, they would have more than 4294 transactions EACH before sid > > filled up. If you are still worried about running out of numbers in your > > sid field make it an unsigned bigint. The max value for an unsigned > bigint > > is 18446744073709551615... so that should give you lots of space to play > in, > > Well, oke. I'm builing some pageviewcounter. Maybe count more then > 1 million pageview a day in future :) You're right it will take many days > before i have counted 18446744073709551615 views :). But in some way it feel > like wasting. The first day using the first milion ids, the second day the > next milion and never reuse that numbers. (Yes i realize 8bytes a row is > much better then 32 (or 33 if you do stupid things :))) > > Maybe there is a better implemention? I dont i the first one solving this? This is usually viewed as a non-issue. The reason is you are using 8 bytes whether sid=1 or (2^64-1) (which is the really big number above). In addition (2^64-1) is a REALLY big number. If you had a Billion hits a day, you would not run out of possible sid's for approximately 50.5 million years. Finally, since you are deleting rows after a certain amount of time you can always reset the auto_increment value (I think that is as simlple as "set insert_id=1;" but you may want to double check as this is off the top of my head). > > > and sid will only be 8 bytes per row... not 33 (Also incidentally, if you > do > > want to save an md5 value to a row in the future... use char not varchar > as > > you know it will be 32 bytes and are adding a wasted 33rd byte). > > Oke, i see. <snip> > > Best regards, > > Bas Sheridan Saint-Michel Website Administrator FoxJet, an ITW Company www.foxjet.com