Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
key size per row? does ndbcluster have a hidden key column? I thought the index was stored separately. –  Ben Mar 15 '12 at 13:36

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

share|improve this answer
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 Mar 15 '12 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' Mar 15 '12 at 14:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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