Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- 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 −
Query
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;
Here,
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.
512 Views
