CODEWITHSUNDEEP
mysqldatabase-design
user441058
user441058

Reputation: 1278

How to show size of row definition in MySql

I'm hitting the 65K max size while creating my table definition. So I'm changing some varchars(1000) to text to reduce the size.

I'm trying to find the current size of my table definition so I can track how close I am to the 65K limit as I add more fields. All my online searches show how to get average row size, but that is based on actual data usage in the row and not the row definition.

Is there a query I can run to show how close I am to the 65K limit on the table row size?

Upvotes: 0

Views: 424

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562991

I did this test:

mysql> create table test.foo ( i int, bi bigint, si smallint, d date, dt datetime, dt6 datetime(6), v varchar(64), f float);

mysql> select coalesce(column_name, 'TOTAL =') as column_name, data_type,
  sum(case data_type
    when 'tinyint' then 1
    when 'smallint' then 2 
    when 'int' then 4 
    when 'bigint' then 8 
    when 'float' then 4 
    when 'date' then 3 
    when 'datetime' then 8 
    when 'timestamp' then 4 
    when 'time' then 3 
    when 'decimal' then 4 
    else least(character_octet_length, 768) 
  end) as size
from information_schema.columns where table_schema='test' and table_name='foo'
group by column_name with rollup;

This is a rough estimate, not intended to be complete. For example, it doesn't handle datetime/time/timestamp with extra bytes for precision. And it assumes decimal is always 4 bytes.

See https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html for more details on storage of different data types.

The output is:

+-------------+-----------+------+
| column_name | data_type | size |
+-------------+-----------+------+
| bi          | bigint    |    8 |
| d           | date      |    3 |
| dt          | datetime  |    8 |
| dt6         | datetime  |    8 |
| f           | float     |    4 |
| i           | int       |    4 |
| si          | smallint  |    2 |
| v           | varchar   |  256 |
| TOTAL =     | varchar   |  293 |
+-------------+-----------+------+

Notice that the varchar is counted as 4x the varchar length. This is because I am using utf8mb4 as my default character set, so octet_character_length assumes up to 4 bytes per character.

In InnoDB, you'll run into the 8KB size limit long before you hit the 64KB row size limit imposed by the MySQL storage-independent layer.

Varchar, varbinary, blob, text and all their cousins behave the same way in InnoDB's COMPACT row format:

The first 768 bytes of any of these string types is stored in the row. Data in excess of 768 bytes is stored on other pages elsewhere in the database, and don't count toward the row size limit.

So you can use InnoDB's DYNAMIC row format to store those string types differently. If the row gets too large, it'll move string columns entirely to overflow pages, and leave only a 20-byte pointer in the row for each string.

See https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html for more documentation on row formats.

Upvotes: 3

Related Questions