2.16.3 String Column Encoding

• To identify explicitly encoded string columns in tables on the MySQL DB System, query the COLUMN_COMMENT column of the INFORMATION_SCHEMA.COLUMNS table. For example:

  Press CTRL+C to copy
   
  mysql> SELECT COLUMN_NAME, COLUMN_COMMENT 
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME LIKE 'orders' AND COLUMN_COMMENT LIKE '%ENCODING%';
  +-----------------+------------------------------+
  | COLUMN_NAME     | COLUMN_COMMENT               |
  +-----------------+------------------------------+
  | O_CLERK         | RAPID_COLUMN=ENCODING=SORTED |
  | O_ORDERPRIORITY | RAPID_COLUMN=ENCODING=SORTED |
  | O_ORDERSTATUS   | RAPID_COLUMN=ENCODING=SORTED |
  | O_CLERK         | RAPID_COLUMN=ENCODING=SORTED |
  | O_ORDERPRIORITY | RAPID_COLUMN=ENCODING=SORTED |
  | O_ORDERSTATUS   | RAPID_COLUMN=ENCODING=SORTED |
  +-----------------+------------------------------+

  You can also view explicitly defined column encodings for an individual table using SHOW CREATE TABLE.

• To view the dictionary size for dictionary-encoded columns, in bytes:

  Press CTRL+C to copy
   
  mysql> USE performance_schema;
  mysql> SELECT rpd_table_id.TABLE_NAME, rpd_columns.COLUMN_ID, rpd_columns.DICT_SIZE_BYTES
            FROM rpd_table_id, rpd_columns 
            WHERE rpd_table_id.ID = rpd_columns.TABLE_ID AND rpd_columns.DICT_SIZE_BYTES > 0 
            ORDER BY rpd_table_id.TABLE_NAME;
  ------------+-----------+-----------------+
  | TABLE_NAME | COLUMN_ID | DICT_SIZE_BYTES |
  +------------+-----------+-----------------+
  | orders     |         3 |        25165912 |
  +------------+-----------+-----------------+