mysql: find column data size on disk

Question

I have fairly large MyISAM table with longtext field in it

Table size is somewhat 16GB.

I need to know if clearing this column in old records helps to keep table small

I can retrieve table wise Data_length using information_schema.tables

Is there a way to find Data_length just for this one column?

Answer 1

Here is how you find the sum of the lengths the LONGTEXT column of all rows

SELECT SUM(field_length) TotalFieldLength FROM
(SELECT LENGTH(LongTextColumnName) field_length FROM mydb.mytable) A;

Once you delete the rows you want, just compress the file

You can do it in a single command

OPTIMIZE TABLE mydb.mytable;

or in stages

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
ALTER TABLE mydb.mytablenew DISABLE KEYS;
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytablenew ENABLE KEYS;
ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;
ANALYZE TABLE mydb.mytable;

Give it a Try !!!