Hi all, I have an Innodb database (MySQL 4.0.21 on Linux). The db is made of 11 tables. 10 of these tables are just a few MBs altogether, while the 11th table, due to a longtext field where binary data is stored, is about 3GBs and counting (about 80MBs inserted everyday).
Since Innodb doesn't seem to reuse disk space efficiently, a day will come when I will have to rebuild the table, both to improve performance and to reclaim disk space. Altering to MyISAM and back is an option I'd rather not choose because it would take a lot to perform 2 conversions, and laso I'd need to recreate foreign constraints, and to have more disk space. Heikki's advice of running: ALTER TABLE <tablename> TYPE=Innodb sounds good, but there's a couple of issues I haven't found an answer to: 1) I suppose the above statement rebuilds the table from scratch. The "new" table will be created starting from the 1st free data page in the tablespace, is this correct? In this case I'll need to issue the "ALTER TABLE" command before the table's data length >= 0.5 * available disk space, is this correct? 2) Once I have rebuilt the table, assuming the above is true, will the space formerly occupied by the "old" table be entirely returned to the free pool, i.e. added to "innodb free"? This is important because though the "new" table would be optimized, I'd have a huge space waste if the answer to this questio is "no". Thanks for any input Stefano -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
