Hi All, I have MyISAM table with auto_increment property.
CREATE TABLE rcatdb_categories ( ID bigint(15) NOT NULL auto_increment, ... PRIMARY KEY (ID), ... however I would like to create as smaller as possible index for ID (by default this is automaticaly done with PRIMARY KEY..), but bigint(15) is too big, I would like to use for example MEDIUMINT or whatever smaller. The problem here is that auto_increment value can become to fast to the limit of MEDIUMINT, because I will have often insert/delete statment. For *example* if the limit of *some* column type is ID=65535 and I have insert/delete 10000 rows for 5 days I will not be able to insert new rows. Moreover I will put every day in DB aprox. 10000 free records (because I will insert 10000 rows and after that I *could* delete 10000 rows too, but table never will become empty), so there will be available aprox 55000 free indexes but the next auto_increment value will be 65536 (i.e every day auto_increment value will be inc. with 10000 and one pretty day auto_increment will become over than 65535 - limit, but in the table I will have only 10000-15000 rows)! If I chose another coulumn type for example with 4G limit the size of INDEX will become bigger and slower. Another way is to strart with SMALLINT..and when auto_increment takes to the limit of maximum allowed value I could do automatic ALTER and substitute SMALLINT with MEDIUMINT...after that with BIGINT..and so on... but if my table has too small rows..for example 2 milion I would not like to use BIGINT only because value of auto_increment :-( I know about myisamchk and --set-auto-increment[=value] option.. so how I could reset safely auto_increment value using SQL query? I will find min value with SELECT MIN(ID), but how to update auto_increment value for respective table? __________________________________________ 12MB-POP3-WAP-SMS-AHTИCПAM--TOBA-E-mail.bG ------------------------------------------ HOB БEЗПЛATEH AДPEC - http://mail.bg/new/ ------------------------------------------ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
