0

Background Info

Let's say I have a table in MySQL that has +1 billion records. I have a column with that contains some description of a post. We don't index, search, or sort this column in any way.

Currently the description column has an encoding of utf8, which in MySQL doesn't support the full unicode standard. I want to add support for emojis, other languages, etc. so I need the column to be utf8mb4

Question

If I run something along the lines of:

ALTER TABLE
user_posts
CHANGE post_description 
VARCHAR(300)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

What actually happens in the background, and would it have a massive performance impact during the operation on a very large table with (+1 billion rows)?

Since there is no index, sorting, or searching in this column, would it need to modify each record? oOr since the current utf8 records won't be changing will it just change the table schema. O(n) or O(1)

1

First, a syntax error: it's MODIFY, not CHANGE, unless you are changing the column name, too.

I ran an experiment with only 1/2 million rows. ALTER to change one column from utf8 to utf8mb4 took 1.6 seconds. I conclude that the table was copied over; that is O(n).

When the developers added the various speedups for ALTER, I suspect they overlooked the case of utf8->utf8mb4, or decided that it was a risky special case -- what if later the charsets diverged? Or the collations diverged? Granted, without any index on the column, that should not matter. (But that would be more more thing to test for before doing the Alter some faster way.)

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.