Hi, The table has 76 columns in it, there are 35 columns are not populated yet but they are all in VARCHAR format (NOT FIXED-SIZE columns). Does it still take space even in VARCHAR format?
Let's say the 35 columns take space, the worst case will be doubling the space like myISAM table is taking that roughly around 500M in disk space, why it took 2.3G instead? When altering a table(remove index), mySQL handles myISAM type table by copying it to temporary table then remove the index saving in MYI file. How InnoDB handles removing index? Similar way? Are you saying I should drop the index from an InnoDB table by doing: alter table mytable drop index idx1, type=myISAM Any suggestions are greatly appreciated. Wai Zeborg -----Original Message----- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Friday, November 02, 2001 10:59 AM To: [EMAIL PROTECTED] Subject: Re: InnoDB disk full problem Wai, >Hi, > >Help!!! Can anyone kindly tell me why InnoDB will take up 10 times table >space comparing with myISAM's table file? I only have one index built in >this table and I don't think it took a lot of table space. The table has >543009 records and has a file size 256M in myISAM but it takes 2.3G table >space in InnoDB. what is a typical row like? I calculate from above that a typical row takes 500 bytes in MyISAM. Does it have many NULL columns? In InnoDB a NULL stored in a fixed-size column takes the same size as a non-NULL value (this is to avoid fragmentation). What is the table CREATE statement? >When I try to drop the index from InnoDB, it gives me a error message: > >ERROR 1114: The table '#sql-258_4' is full ALTER TABLE in MySQL is performed by creating a temporary table where data is copied. You should try ALTER TABLE ... TYPE = MYISAM. >The InnoDB is a very new concept to me, I am testing mysqld-max-nt 3.23.43 >on Windows 2000(512M memory 833MHz CPU) with the following configuration: > >set-variable= key_buffer=32M >set-variable= max_allowed_packet=1M >set-variable= table_cache=256 >set-variable= sort_buffer=1M >set-variable= record_buffer=1M >set-variable= myisam_sort_buffer_size=16M >set-variable= thread_cache=8 > >innodb_data_home_dir = c:\ibdata >innodb_data_file_path = ibdata1:1500M;ibdata2:1500M >set-variable = innodb_mirrored_log_groups=1 >innodb_log_group_home_dir = c:\iblogs >set-variable = innodb_log_files_in_group=3 >set-variable = innodb_log_file_size=50M >set-variable = innodb_log_buffer_size=8M >innodb_flush_log_at_trx_commit=0 >innodb_log_arch_dir = c:\iblogs >innodb_log_archive=0 >set-variable = innodb_buffer_pool_size=300M >set-variable = innodb_additional_mem_pool_size=20M >set-variable = innodb_file_io_threads=4 >set-variable = innodb_lock_wait_timeout=50 >set-variable=tmp_table_size=500M > >Wai Lee >Zeborg Regards, Heikki http://www.innodb.com/ibman.html --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
