How to compress columns in MySQL?

11,625

Solution 1

You are probably looking for MySQL COMPRESS() and UNCOMPRESS() function to compress data for storage and retrieval respectively.

Also look at InnoDB Compression Usage.

Solution 2

As long as the data doesn't need editing, you can use the archive engine.

Solution 3

For me the best way to use text data compression is to use a Percona compressed column format.

ALTER TABLE `tableName` MODIFY `mail` TEXT COLUMN_FORMAT COMPRESSED NOT NULL;

I've tested compression on table used as cache, storing mainly HTML data, the size decreased from 620 MB to 110.6MB.

I think you should consider using TEXT type instead of long VARCHAR. Data fields are stored separately from innodb clustered index and it can affect and probably improve the performance of your database.

Solution 4

This answer is specific to Percona

Percona introduced a compressed column format a while ago. That you can use on CREATE or ALTERs

CREATE TABLE test_compressed (
    id INT NOT NULL PRIMARY KEY,
    value MEDIUMTEXT COLUMN_FORMAT COMPRESSED
 );

Reference: https://www.percona.com/doc/percona-server/5.7/flexibility/compressed_columns.html

Share:
11,625
MontyPython
Author by

MontyPython

Updated on June 04, 2022

Comments

  • MontyPython
    MontyPython almost 2 years

    I have a table which stores e-mail correspondences. Every time someone replies, the whole body of the trail is also included and saved into the database (and I need it that way because the amount of application level changes to rectify that are going to be too high).

    The size of the mail text column is 10000. But, I am having trouble storing text more than that. As I am not sure, how many correspondences can occur, I don't know what a good number will be for the column.

    The engine is InnoDB. Can I use some kind of columnar compression technique in MySQL to avoid increasing the size of the column?

    And, what if I go ahead and increase the varchar column to, say, 20000. The table has about 2 million records. Will that be a good thing to do?