MySQL5 - Checking size of each column in a schema

Question

I have 2 MySQL DB's in my environment. One is only 40Mb in size and other is 20Gb. They belong to a business objects application called infoview. The one at 40Mb is the correct size and is the working infoview application.

I want to find out exactly from the table cms_infoobjects6 what data is using up all that space.

I am speaking with my DB guys and I don't seem to be getting anywhere, he said he could see a longblob column but no real information about that.

Answer 1

you can use a query like this. there all all information about any table. you must add INDEX_LENGTH + DATA_LENGTH to get the hole table size.

SELECT * FROM information_schema.tables 
WHERE
  table_schema='yourSchema'
AND
  TABLE_NAME='job';

sample

MariaDB [yourschema]> SELECT * FROM information_schema.tables
    -> WHERE
    ->   table_schema='yourSchema'
    -> AND
    ->   TABLE_NAME='job'\G

*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: yourschema
     TABLE_NAME: job
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 7
 AVG_ROW_LENGTH: 2340
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2016-06-28 11:46:14
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.08 sec)

MariaDB [yourschema]>

Answer 2

Use SHOW CREATE TABLE tablename to display the schema for a given table. That may say LONGBLOB -- which means a column that is allowed to hold up to 4GB of data. It may say VARCHAR(100), a column that is allowed to hold up to 100 characters.

To find the actual size of these columns, you need something like

SELECT LENGTH(col) FROM tablename;

That would provide a list of byte-length for col.

What is the question?