MySQL row length is almost the triple of what I expect

database-sizeMySQLndbcluster

I shamelessly took title and content from this question and adapted it to my similar yet different problem:

This is the table I have:

CREATE TABLE `test` (
    `field1` MEDIUMINT(8) UNSIGNED NOT NULL,
    `field2` INT(10) UNSIGNED NOT NULL,
    `field3` SMALLINT(5) UNSIGNED NOT NULL,
    `field4` SMALLINT(5) UNSIGNED NOT NULL,
    `field5` FLOAT UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`field1`, `field2`),
    INDEX `field2` (`field2`, `field1`)
)
COLLATE='latin1_swedish_ci'
ENGINE=ndbcluster
ROW_FORMAT=DEFAULT

I expect the row length to be 15 bytes:

  • 3 bytes for field1 mediumint(8)
  • 4 bytes for field2 int(10)
  • 2 bytes for field3 smallint(5)
  • 2 bytes for field4 smallint(5)
  • 4 bytes for field5 float

But show table status; returns

Name: test
Engine: ndbcluster
Row_format: Fixed
Rows: 143120123
Avg_row_length: 44 <--------------  (I assume this is in bytes?)
Data_length: 7659913216

As you can see, I have quite a few rows, so row length really does matter.

Why does it take so much space?

And why is Data_length so much bigger than expected?

Data_length - (Rows * Avg_row_length) = 7659913216 - 6297285412 = 1362627804 = 1.26 GB

And what are those digits in brackets after the type names for? (e.g. mediumint(8))
I guess it's the char length of the field's string representations and I have the strange feeling that MySQL's ndbcluster engine stores them exactly like that!
Which seems amazingly stupid to me…

Best Answer

I don't think I've ever come across any description of how ndbcluster affects row layout. (I haven't looked hard, though. Also haven't read the source code.) But, for example, innodb tables have a well-documented row layout. (Innodb row structure.) You can find quite a few things that affect the overall length of a row.

Related Question