4

I have a big table with BLOB fields that contain many NULL values. I enabled Multiple Tablespaces and got a separate ibd file for each table. The physical size of this table is much more than the size I calculate myself, so my conclusion is that it should be possible to shrink this ibd file, but I am still not able to do this, even after reloading a dump file. I verified the dump file really contains SQL statements that inserts NULL values. Any tips to shrink physical size of this table? Or is this impossible because MySQL allocates a fixed size for blobs?

Hans Verlouw
  • 41
  • 1
  • 1
    I thought I already tried to set the ROW_FORMAT to COMPACT and did not work, but now it is working. Both the Antelope and Barracuda file format shrink the file: ALTER TABLE DOC ENGINE=InnoDB ROW_FORMAT=COMPACT; but even if I just say: ALTER TABLE DOC ENGINE=InnoDB; – Hans Verlouw Apr 24 '12 at 15:57
  • You have obviously tried OPTIMIZE TABLE after your Multiple Tablespaces were enabled? You may also need to delete files manually when re-importing from SQL dump. – Yuriy Oct 04 '12 at 10:24
  • According to [the doc](http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html), MySQL should not be allocating a fixed amount of disk for your variable-length column. Post the data from SHOW TABLE STATUS. – Alain Collins Nov 09 '12 at 18:43

1 Answers1

0

Drop indexes if any on BLOB columns Please provide o/p of SHOW TABLE STATUS LIKE '<table_name>'

Karthik Appigatla
  • 156
  • 1
  • 8