Optimal data types for UUID and ENUM AWS Aurora for MySQL table DDL

Question

Hi i have a table in MySQL 5.7 Aurora and current data types for the table is

CREATE TABLE `table` (
`ID` varchar(255) NOT NULL,
`USER_ID` varchar(255) NOT NULL,
`PATTERN_TYPE` varchar(255) NOT NULL,
`USES_TYPE` varchar(255) NOT NULL,
`OBJECT_TYPE` varchar(255) NOT NULL

PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

NOTE :I need to have Index on USR_ID column

To use Aurora parallel query for this table i need to change varchar(255) to varchar(192) I can do that for ID columns but before doing just wanted to know if i can optimize further ?

I have ID which is UUID generated from application and it is of two kind

6tgbcrq9pfhtyizsdo82mcrzz
0a3687fr-612f-2345-984c-65c100076267

so for above two kind of UUID we can use varchar(192) or even we shrink more ?

Query 2 : We have fixed types for PATTERN_TYPE (88 distinct types) ,USES_TYPE(5 distinct types) and OBJECT_TYPE (10 distinct) . what would be the best data types for this two columns ? Can we use ENUM for this ?

Why need to convert to varchar(192 ) because of below limitation from Audora MySQL

TEXT, BLOB, JSON, and GEOMETRY data types aren't supported with parallel query. A query that refers to any columns of these types can't use parallel query.

Variable-length columns (VARCHAR and CHAR data types) are compatible with parallel query up to a maximum declared length of 768 bytes. A query that refers to any columns of the types declared with a longer maximum length can't use parallel query. For columns that use multibyte character sets, the byte limit takes into account the maximum number of bytes in the character set. For example, for the character set utf8mb4 (which has a maximum character length of 4 bytes), a VARCHAR(192) column is compatible with parallel query but a VARCHAR(193) column isn't.

Answer 1

The string 0a3687fr-612f-2345-984c-65c100076267 is only 36 characters long. Why would you need varchar(255) or varchar(192) for that?

If you need it to be even more compact, you can use VARBINARY(16). Then for your UUID, remove the - characters and convert the string to bytes using UNHEX().

For your other columns pattern_type, uses_type, and object_type, I don't recommend using ENUM for columns that are likely to require new values from time to time. I'd use ENUM only when the column is certain to be unchanging.

It would be better to define a lookup table for those types, with a shorter ID. For example, either an integer or a short code. Then you can make reference to it in your table shown above, using a smaller data type.