COMPACT Row Format •Internally, fixed-length character columns such as  CHAR(10)  in stored in fixed-length format. Trailing spaces are not truncated from  VARCHAR  columns. •Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns, which can be stored off-page. For example, a  CHAR(255)  column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with  utf8mb4 . •An SQL  NULL  value reserves one or two bytes in the record directory. An SQL  NULL  value reserves zero bytes in the data part of the record if stored in a variable-length column. For a fixed-length column, the fixed length of the column is reserved in the data part of the record. Reserving fixed space for  NULL values permits columns to be updated in place from  NULL  to non- NULL  values without causing index page fragmentation. COMPACT Row Format The  COMPACT  row format reduces row storage space by about 20% compared to the  REDUNDANT  row format, at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed,  COMPACT  format is likely to be faster. If the workload is limited by CPU speed, compact format might be slower. Tables that use the  COMPACT  row format store the first 768 bytes of variable-length column values ( VARCHAR ,  VARBINARY , and  BLOB  and  TEXT  types) in the index record within the B-tree node, with the remainder stored on overflow pages. Fixed-length columns greater than or equal to 768 bytes are encoded as variable-length columns, which can be stored off-page. For example, a  CHAR(255)  column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with  utf8mb4 . If the value of a column is 768 bytes or less, an overflow page is not used, and some savings in I/O may result, since the value is stored entirely in the B-tree node. This works well for relatively short  BLOB  column values, but may cause B-tree nodes to fill with data rather than key values, reducing their efficiency. Tables with many  BLOB  columns could cause B-tree nodes to become too full, and contain too few rows, making the entire index less efficient than if rows were shorter or column values were stored off-page. COMPACT Row Format Storage Characteristics The  COMPACT  row format has the following storage characteristics: •Each index record contains a 5-byte header that may be preceded by a variable-length header. The header is used to link together consecutive records, and for row-level locking. •The variable-length part of the record header contains a bit vector for indicating  NULL  columns. If the number of columns in the index that can be  NULL  is  N , the bit vector occupies  CEILING( N /8)  bytes. (For example, if there are anywhere from 9 to 16 columns that can be  NULL , the bit vector uses two bytes.) Columns that are  NULL  do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are  NOT NULL  and have a fixed length, the record header has no variable-length part. •For each non- NULL  variable-length field, the record header contains the length of the column in one or two bytes. Two bytes are only needed if part of the column is stored externally in overflow pages or the maximum length exceeds 255 bytes and the actual length exceeds 127 bytes. For an externally stored column, the 2-byte length indicates the length of the internally stored part plus the 20-byte pointer to the externally stored part. The internal part is 768 bytes, so the length is 768+20. The 20-byte pointer stores the true length of the column. •The record header is followed by the data contents of non- NULL  columns. 3167