Query to calculate Mysql row max size

Question

Say I have a db table with 5 fields.

id bigint(20)
name varchar(255)
place varchar(255)
DOB date
about TEXT

Maximum row size is 65535 bytes in MySQL that is shared among all columns in the table, except TEXT/BLOB columns.

I need a query which will give me the maximum row size in bytes for this table so that I can check if the max size exceeds 65535. The charset used is utf8mb4. I need something like this

(20*8 + 255*4 + 255*4 + 3) = 2203
the size of about field is ignored since the type is TEXT.

You can query `INFORMATION_SCHEMA.COLUMNS`. Use the `DATA_TYPE` and `CHARACTER_MAXIMUM_LENGTH` columns. — Barmar, Feb 10 '20 at 13:53
I don't think there's anything built-in that will return the size of a datatype. You can use a `CASE` expression or create a table of your own that maps type names to size. — Barmar, Feb 10 '20 at 13:55

Rick James · Answer 1 · 2020-02-16T20:05:26.867

It is much simpler -- Write and run the CREATE TABLE statement. It will spit at you if it is too large.

Meanwhile, here are some of the flaws in your attempt:

BIGINT is 8 bytes; the (20) is useless information.
Each VARCHAR needs 1- or 2-bytes for length field.
TEXT takes some amount of space toward the 64K limit.
There is a bunch of "overhead" for each column and the row, so it is really impractical to try to compute the length.
Note also -- When the length gets too large, some of the VARCHARs may be treated like TEXT. That is they won't necessarily count toward the 64K limit. However, they will leave a 20-byte pointer in their place.

If you are tempted to have a table with too many too-big columns, describe the table to us; we can suggest improvements, such as:

BIGINT is rarely needed; use a smaller int type.
Don't blindly use (255) is has several drawbacks. Analyze your data and pick a more realistic limit.
Be aware that there are four ROW_FORMATs. DYNAMIC is probably optimal for you. (Old versions of MySQL did not have Dynamic.
Parallel tables (Two tables with the same PK)
Normalization (Replacing a common string with a smaller INT.)
Don't use a bunch of columns as an "array"; make another table. (Example: 3 phone number columns.)

Have you hit any INDEX limits yet? It smells like you might get bitten by that, too. Write up the CREATE TABLE, including tentative indexes. We'll chew it up and advise you.

Query to calculate Mysql row max size

1 Answers1