1

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

Improve this question
1
  • key size per row? does ndbcluster have a hidden key column? I thought the index was stored separately.
    – Ben
    Commented Mar 15, 2012 at 13:36

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • 1
    I just did a test with using InnoDB as engine resulting in an Avg_row_length of 87!!! Using MyISAM I get 17. Why?
    – Ben
    Commented Mar 15, 2012 at 13:29
  • I don't know where to find documentation for the row layout for ndbcluster. For innodb, though, two indexes adds 12 bytes (index headers), add 5 bytes for record pointers, clustered index (primary key) adds 15 bytes (? fields for all user-defined columns) plus 13 bytes (transaction ID and roll pointer). So, at least 60 bytes, if I've understood the documentation correctly. You can read what I've read. (Linked in my answer.)
    – Mike Sherrill 'Cat Recall'
    Commented Mar 15, 2012 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.