What is the maximum optimal size of MYSQL PRIMARY CHAR KEY

user_1601656 0
Viewed 114

My main issue is that after we expanded CHAR(50) to CHAR(64) we started receiving timeouts on internal backup queries. The record size is a few kb and the database is very very big, so this column that is a primary key must be the reason for our trouble.

I searched through the internet but I found only selecting the type of the keys or comparison of CHAR vs VARCHAR but nothing about the optimal size. For example, is there some special optimization in MYSQL that for indices smaller than, let's say, 60 bytes it uses some for of caching while for larger it starts swapping stuff?

Any help would be appreciated. Even those suggesting there is no difference and simply the % of time spent on join was increased by % of the index size.

EDIT

THIS IS NOT THE ANSWER FITTING THE QUESTION however I have found out the reason our change got a HUGE performance hit.

We expanded column using

ALTER TABLE table MODIFY job_id CHAR(64);

This caused CHARACTER SET fall back to the default one = utf8mb4 dropping previous latin1

That would conclude my research, but I will leave this question opened for anyone that would be able to answer the impact on resizing key column.

This question does look for suggestion on type change.

Thank you all for their time and inputs!

indexing join mysql performance
1 Answers

Short answer: There is no caching/swapping/optimal/etc size.

Long answer:

Don't use CHAR unless the data for that column really is fixed length -- such as country_code, postal_code, UUID, SSN, etc. Furthermore, use the minimal charset needed, such as ascii for those. CHAR wastes space by padding with spaces.

There is no such cutoff. There is no inherent problem in having a long PRIMARY KEY except that ...

  • Every secondary key has a copy of the PK. (This says the break-even is at 2 indexes; for more than 2, the extra bulk in secondary keys adds up for long PKs.)
  • Columns in other tables that need to JOIN to this PK (with or without an Foreign Key declared) will be bulkier than an INT.

Many users (or 3rd party software that generates SQL) blindly uses 8-byte BIGINT for ids. Even the 4-byte INT is usually overkill; see the smaller INT types.

Indexes are limited, but many things factor in:

  • 767 / 1000 / 3072 bytes depending on engine and version
  • Character set of char/varchar: CHAR/VARCHAR(50) may take 50 / 100 / 150 / 200 bytes, depending on charset.

InnoDB's buffer_pool is limited by innodb_buffer_pool_size, which should be set to something like 70% of RAM, does have caching. This implies that the bigger a table or index is, the more I/O is likely to be done.

Bottom line: Your timeouts are coming from other things. Consider increasing the timeout.

Also

When doing ALTER TABLE ... MODIFY COLUMN ..., you must specify all the characteristics of the column, specifically including the ones you are not changing. These include CHARACTER SET, COLLATION, [NOT] NULL, DEFAULT, etc.

I like to do SHOW CREATE TABLE to get the current definition for the columns, then copy the one I want to change into my fresh ALTER, modifying the one thing I am changing.

user_17668310
Related