MyISAM table row size is bigger than the sum of all the columns size

myisam

I have a table:

CREATE TABLE `playlist` (
  `pid` int(11) unsigned NOT NULL,
  `mid` bigint(21) unsigned NOT NULL,
  `status` tinyint(1) NOT NULL,
  `add_time` int(11) unsigned NOT NULL,
  `modify_time` int(11) unsigned DEFAULT NULL,
  `filename` char(255) NOT NULL,
  `source` tinyint(3) unsigned NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

From show table status, I got the "Avg_row_length: 788". But the sum of the all the columns is 277 bytes.So I am confused that why the real row size is much larger then the sum of all columns'size?

I hexdump the data file and find the tail of the row is padded by space char:

00000000  fd 63 00 00 00 f2 3c 08  00 00 00 00 00 01 33 f4  |.c....<.......3.|
00000010  c7 4b 33 64 c8 4b 31 32  36 39 35 37 35 37 34 37  |.K3d.K1269575747|
00000020  38 31 37 2e 6d 70 33 20  20 20 20 20 20 20 20 20  |817.mp3         |
00000030  20 20 20 20 20 20 20 20  20 20 20 20 20 20 20 20  |                |
*
00000310  20 20 20 01                                       |   .|
00000314

MySQL add many spaces into the tail of the row, I don't know why there are so many additional space characters.

Best Answer

That's because you're using a CHAR column type; this is a fixed-width column; regardless of how much data you put in there, it'll always take up exactly that number of characters in the data store. It is designed for columns where you'll always have exactly that number of characters, because it's more efficient in that specific case.

What you want in your case is VARCHAR; it'll make a note in the column data of how many characters you're storing, and only take up that amount of space in the data store.

Related Question