No, you understood me wrong. MEDIUMINT takes only 8 bytes, but BIGINT takes 20, so the whole DB grows, moreover in Mysql documentation I read about indexes and column optimization and thay sad: medium int is always better than int - in context of that article. I would like to do as small as possible my db, so select-s to be much faster using index/key. However I would like to support a large number of possible rows(to be inserted in respective tables). Also auto_increment "remember" the biggest value, which not means that table can't takes no more rows, i.e. INSERT_ID = MAX_VALUE and I could not insert new rows, no matter whether I have only 1 row in that table...
So the only reasonable solution was to ALTER table whenever ID reach the maximum available value for respective column type. I could start with MEDIUMINT type and finish with BIGINT, but I'm wondering is it another way to do that? > > Hi Julian, > > If I understand your question, you have mistaken how indexes work. The > size > of an index field depends upon the number of entries in it, not the size > of > the largest/smallest entries. There is no such thing as "free indexes" > in > MySQL. The index is not an array, it is a b-tree. So as long as the > total > number of entries in your table stays the same, so will (roughly) the > size > of the index. You can check this by doing the following: > > create table indexed ( > a bigint(15)not null, > primary key (a) > ) ; > insert into indexed values (0), (1234567890123) ; > > then checking the size of the indexed.MYI file. It is only 2048 bytes > (1 > block) on my system, not 1234567890123x15 bytes as you seem to think, > and > it probably won't get any bigger until > you put in about 100 rows. > > Alec > > Mysql > > ----------------------------------------------------------------------- ----------------- > > > 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 > > > > __________________________________________ 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
