- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Enable MySQL Compression
Before a compressed table is created, ensure that the innodb_file_per_table configuration option is enabled, and innodb_file_format is set to Barracuda. These parameters can be found in the MySQL configuration file my.cnf or my.ini, or with the SET statement without having to shut down the MySQL server.
To enable compression for a table, the clauses ROW_FORMAT=COMPRESSED, KEY_BLOCK_SIZE, or both can be used in a CREATE TABLE or ALTER TABLE statement.
Let us see the statements to create a compressed table −
SET GLOBAL innodb_file_per_table=1; SET GLOBAL innodb_file_format=Barracuda; CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
If ROW_FORMAT=COMPRESSED is specified, the KEY_BLOCK_SIZE can be omitted.
The default page size value is used, and this is equivalent to half the innodb_page_size value.
If the KEY_BLOCK_SIZE is specified, the ROW_FORMAT=COMPRESSED can be omitted.
The compression is enabled automatically.
To determine the best value for the KEY_BLOCK_SIZE attribute, the user needs to create multiple copies of the same table with different values for this clause.
Once this is done, measure the size of the resulting .ibd files and determine how well each of this performs with a realistic workload.
The KEY_BLOCK_SIZE value is considered as a hint.
If innodb_strict_mode=ON, if an invalid KEY_BLOCK_SIZE value is specified, it returns an error.
The default uncompressed size of InnoDB data pages is considered as 16KB. Depending on the combination of option values, MySQL uses a page size of 1KB, 2KB, 4KB, 8KB, or 16KB for the .ibd file of the table.
The actual compression algorithm doesn’t get affected by the KEY_BLOCK_SIZE value.
All indexes of a table, including the clustered index will be compressed using the same page size, which is specified in the CREATE TABLE or ALTER TABLE statement. Table attributes like ROW_FORMAT and KEY_BLOCK_SIZE are not considered to be a part of the CREATE INDEX syntax for InnoDB tables. Hence, they are ignored if they are specified.
- How To enable GZIP Compression in PHP?
- Difference between Lossy Compression and Lossless Compression
- Enable TLS for MySQL Clients
- How to enable MySQL Query Log?
- PHP compression Stream Wrappers
- What is DjVu Compression?
- Data compression in SAP HANA
- How can I enable MySQL slow query log without restarting MySQL?
- Data Compression in SAP HANA system
- Python Support for bzip2 compression (bz2)
- How can I install or enable innoDB in MySQL?
- Compression compatible with gzip in Python (zlib)
- Program to perform string compression in Python
- How can we ENABLE AND DISABLE a particular MySQL event?
- Compression using the LZMA algorithm using Python (lzma)