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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

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

migrated from Aug 17 '11 at 23:06

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

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
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 – embobo Aug 17 '11 at 22:45

Your Answer


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