CODEWITHSUNDEEP
mysql
Hoja
Hoja

Reputation: 1207

Difference in mysql dump file size and database size

Hi folks I've a doubt in mysql file dump size and db size varies.

My DB size is 238MB and same DB's dump size is 297MB. Why this large variations?. Can any one explain this?.

Upvotes: 8

Views: 15250

Answers (3)

Isaac
Isaac

Reputation: 41

For a good normalized db, db size is significantly smaller than dump file.

Because the db uses a lot of numbers, I have a db with 200 mb dump, but only 80 kb in db.

As said by Barmar, during dump, numbers are converted into characters, 1234567891 uses 1 integer for storage, or 3 bytes, but 10 bytes for the characters.

Upvotes: 3

Barmar
Barmar

Reputation: 780779

The dump file is a text file. When you turn the internal data of a DB into text, there are many expansions:

  • Numbers that might be 4 bytes internally are written out as digits like 123456
  • Strings have quotes around them, and special characters in the strings may be escaped
  • There are commas between each column, and parentheses around the rows

Upvotes: 10

praveen_programmer
praveen_programmer

Reputation: 1062

When you run mysqldump, only data is dumped in SQL statements. Indexed are not copied. Indexes get rebuilt when the SQL is loaded into another server.

Upvotes: 8

Related Questions