👋👋 Hello everybody, Today I will share with you some basic knowledge you should know when working with MySQL database 😁
Do you know what default Engine in your MySQL and what is Engine on below code, and do you know what is limit size on table, on record and why use varchar 191, varchar 255 in MySQL?
Don't worry, your answer can find it on this topic
- CSV: CSV storage engine
Because we will working with default INNODB Storage Engine. So I will share with you some noted of this Engine:
We have some limit thing in Engine INNODB
Limit Table : 1017 columns, 64 secondary indexes
* A table can contain a maximum of 1017 columns (raised in MySQL 5.6.9 from the earlier limit of 1000). Virtual generated columns are included in this limit.
* A table can contain a maximum of 64 secondary indexes.
Max Key length = 3500 bytes
The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes.
Limit Varchar size 191 vs 255
When using character-set = utf8mb3 => Limit varchar with Engine = InnoDB is 191 (767 / 4)
When using character-set = utf8 => Limit varchar with Engine = InnoDB is 255 (767 / 3)
* If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.
innodb_large_prefix is deprecated and will be removed in a future release. innodb_large_prefix was introduced in MySQL 5.5 to disable large index key prefixes for compatibility with earlier versions of InnoDB that do not support large index key prefixes.
Index key prefixes can be up to 767 bytes.
The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. Attempting to use an index key prefix length that exceeds the limit returns an error.
Limit record per tables: around 5 billion records with 1 fields 4 bytes
On a Win32 system running NTFS, the maximum table size in MySQL is
around 2TB. Assuming you have a rudimentary table with a single field of
width 4 bytes, this would mean the maximum number of records a table
could have is:
2.000.000.000.000 bytes / 4 bytes per field = 500.000.000.000 = 5 billion records
Limit size log in DB: less than 4GB
The combined size of the InnoDB log files must be less than 4GB.
The minimum tablespace size is 10MB. The maximum tablespace size is four billion database pages (64TB). This is also the maximum size for a table.
Maximum LONGBLOB, LONGTEXT must be less than 4GB
The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB.
To avoid such errors in replication configurations, avoid enabling innodb_large_prefix on the source if it cannot also be enabled on replicas. If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size.
That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB. The limits that apply to index key prefixes also apply to full-column index keys.
Although InnoDB supports row sizes larger than 65.535 internally, you cannot define a row containing
VARBINARY or VARCHAR
columns with a combined size larger than 65.535:
mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
-> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
-> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;
ERROR 1118 (42000): Row size too large. The maximum row size for the
used table type, not counting BLOBs, is 65535. You have to change some
columns to TEXT or BLOBs
After read some database secret thing. Is it useful for you? So if any question and feedback for topic "Some knowledge useful when working with MySQL Database", leave your message we can discuss about it!!
Learn Tech Tips - Zidane
references: http://download.nust.na/pub6/mysql/doc/refman/5.1/en/innodb-restrictions.html
