CODEWITHSUNDEEP
mysql
Freddy
Freddy

Reputation: 137

Why my data is too large relative at really size?

i have problem with my database, i collect dump to other database to analyze it and i have create table to reference date checked so when i see my interface phpmyadmin, i see my table size equal at 16Kio for two date with id !

i saw document here http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html and the date type size is 3 bytes ... the différence is too big, and i have problem with all data ! the size of original dump is 1.9Mo for one dump, in my database the size is 5Mo !

i don't know where does the problem ?

Upvotes: 0

Views: 119

Answers (2)

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4284

There is no problem,

The database in MySQL is not only data, you must consider space used by:

  • Table definition
  • Table indexed (>= table data)
  • Table data

Each field type have different storage methods.

The dump file only contains table definitions and data as SQL inserts.

Checking your db structure and sample dump:

Your database are stored as InnoDB with UTF8, an your dumps are stored using MyISAM with latin1, is a big difference because UTF character sets use more space to store string/varchar data and InnoDB uses additional space because the table is stored physically inside InnoDB files.

I choose a dump file and one table then I create the same table using InnoDB with UTF8, see the difference in sizes:

mysql> call tools.sp_status(database());
+---------------------+--------+-------+---------+-------------------+
| Table Name          | Engine | Rows  | Size    | Collation         |
+---------------------+--------+-------+---------+-------------------+
| BDDJoueurs          | MyISAM | 33981 | 2.47 Mb | latin1_swedish_ci |
| BDDJoueurs_unicode  | InnoDB | 33981 | 6.03 Mb | utf8_unicode_ci   |
+---------------------+--------+-------+---------+-------------------+

I think you are using InnoDB to analyze, maybe is a good idea change your consolidated data to MyISAM preserving your character set.

Note: I use a custom show table status.

Upvotes: 1

mhawke
mhawke

Reputation: 87084

Table size is not a simple multiple of the number of rows, more to do with the page size. 16KB sounds about right for a single page (see mysql documentation). Your two rows would take less than 1 page, so 1 page is sufficient.

There is other overhead too for indexes, metadata, etc.

BTW What is a Mo, a Kio?

Upvotes: 0

Related Questions