0

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.

7
  • How many distinct ENUM values will you have?
    – Rick James
    Apr 16, 2021 at 17:47
  • Why do you "need" to change to 192? 5.7 allows indexing 255, even with utf8mb4. 5.5 and 5.6 may need 191, not 192.
    – Rick James
    Apr 16, 2021 at 17:48
  • @RickJames this is because Aurora parallel query can not work with varchar(255).
    – Anupam
    Apr 16, 2021 at 18:50
  • @RickJames for the distinct ENUM max is 88 distinct values
    – Anupam
    Apr 16, 2021 at 18:52
  • What's the query that you are trying to optimize?
    – Rick James
    Apr 16, 2021 at 19:17

1 Answer 1

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.

2
  • so - one is older data so we can not modify that .And for pattern_type if enum is not suitable what about setting int and may be i can handle this in application map the types ?
    – Anupam
    Apr 16, 2021 at 18:54
  • If you can keep track of the meaning of each integer code in the application, that's a good solution. Apr 16, 2021 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.