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
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.