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

share|improve this question
add comment

migrated from serverfault.com Aug 17 '11 at 23:06

This question came from our site for professional system and network administrators.

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

share|improve this answer
2  
even taking into account the fact that the filename column will always take 255 characters you don't get a sum of 788. The correct answer is that when using the UTF8 character set each char takes 3 bytes, not 1. Taking that into account the row length will in fact be 788. Reference dev.mysql.com/doc/refman/5.0/en/storage-requirements.html –  embobo Aug 17 '11 at 22:45
add comment

Your Answer

 
discard

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