Does MySQL compress the stored data?

Question

When I export a database in MySQL, it gives me a .sql file. Let's say this .sql file is 80 MB.

Are they compressed in order to reduce disk space usage?

Is that 80MB real physical-storage size?

The size in the database might actually be a lot higher because of indexes defined on the tables. The SQL only contains the index definition. The database stores the actual index data. Depending on the table/index size, this could mean that the database is a lot bigger than the 80mb

RolandoMySQLDBA · Accepted Answer · 2012-10-25 04:05:13Z

It depends on how you export your database:

if you use mysqldump, then the result is a flat text file containing the sql commands.

you can compress it manually or in one commandline like this:

mysqldump < mysqldump options> | gzip > outputfile.sql.gz

if your question is, if the tables are stored compressed in the working directory, there are some possibilities:

if you have MyISAM tables that would work if they are read-only, you can Generate Compressed, Read-Only MyISAM Tables with myisampack

And by setting InnoDB configuration options, you can create tables where the data is stored in compressed form.

see: dev.mysql.com/doc/refman/5.5/en/innodb-compression.html

just alter the table to add the compressed option:

ALTER TABLE `tablename` ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE =4

1 Answer