2

I have a table in MariaDB (version 10.3 or later) that stores sent emails. One of the fields is body, of type LONGTEXT, which contains the full HTML content of the email.

This table is growing quite large, and the time to perform backups (dumps) has also increased significantly.

I ran a test using the COMPRESS() function on the content before storing it, and here are the results:

Table size reduced: 4.7x smaller

Dump time reduced: 5x faster

My question is: Is storing the email body compressed using COMPRESS() considered a good practice in this scenario? Are there any important downsides or better alternatives (e.g., moving this field to a separate table, storing the content externally, etc.)?

Additional info:

The body field is not used in searches (no LIKE or FULLTEXT)

Read access is rare, mostly for historical logs

Using MariaDB 10.3 or later

I’d appreciate any insights or suggestions for better handling large tables with rarely accessed data.

Rafael Busz
  • 43
  • 7

1 Answers1

4

I would say, yes, this is a good idea. However, maybe a slightly better idea could be to define the column as compressed via a column option:

CREATE TABLE email (
  id int unsigned AUTO_INCREMENT PRIMARY KEY,
  subject varchar(400),
  body LONGTEXT COMPRESSED
);

That way you get the compression automatically without having to call a special function: Both compression and decompression happen automatically.

Note that you can also easily change a column that was originally not defined as compressed to become compressed:

ALTER TABLE email
  MODIFY body LONGTEXT COMPRESSED;

Documentation page here: Storage-Engine Independent Column Compression

I can also add that, anecdotally, I have used this solution myself in a production environment with success for a number of years now.

This feature appeared first in MariaDB 10.3. MySQL does not (yet) have this feature.

Another option could be using the MariaDB InnoDB compressed row format although the documentation page warns us:

The Compressed row format does not efficiently use the InnoDB buffer pool, so it is not the recommended way to achieve InnoDB table compression.

(MySQL also support InnoDB table compression.)

(This answer has been updated with a few details from Rick's comments.)

dbdemon
  • 6,964
  • 4
  • 21
  • 40