Thesql: find column data size on disk

myisamMySQL

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?

Best Answer

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 !!!

RECOMMENDATION #1

Let's presume you have a fair amount of disk space on your cloud server. You create the InnoDB table in stages to watch the effect of diskspace and RAM in slow motion.

Let's say your MyISAM table is called mydb.mytable and you want to convert it to InnoDB.

Try the following:

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ENGINE=InnoDB;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Play with the table.

If you like its performance, then

DROP TABLE mydb.mytable_old;

If you do not like its performance, then

TRUNCATE TABLE mydb.mytable_old;
INSERT INTO mydb.mytable_old SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_zap;
ALTER TABLE mydb.mytable_old RENAME mydb.mytable;
DROP TABLE mydb.mytable_zap;

RECOMMENDATION #2

You mentioned you are running MySQL 5.1. Unless you install the InnoDB Plugin, InnoDB is single threaded. To get InnoDB to be multithreaded:

Install MySQL 5.6
Install MySQL 5.1's InnoDB Plugin if you want to keep MySQL 5.1 (Applicable to MySQL 5.1.38+)

Once you do either one, please configure InnoDB to engage multiple cores. See my past posts:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

Best Answer

Related Solutions

Mysql takes up to 50% cpu after aborting DELETE query

MySQL – Optimal Configs for ALTER of Large MyISAM Table to InnoDB

RECOMMENDATION #1

RECOMMENDATION #2

Related Question