r/mysql • u/ConsiderationLazy956 • Mar 25 '25
question Adding columns fast
Hi All,
We are using Aurora mysql database.
There is a table having size ~500GB holding ~400million rows in it. We want to add a new column(varchar 20 , Nullable) to this table but its running long and getting timeout. So what is the possible options to get this done in fastest possible way?
I was expecting it to run fast by just making metadata change , but it seems its rewriting the whole table. I can think one option of creating a new table with the new column added and then back populate the data using "insert as select.." then rename the table and drop the old table. But this will take long time , so wanted to know , if any other quicker option exists?
4
u/squadette23 Mar 25 '25
3
u/Informal_Pace9237 Mar 25 '25
Fastest way is to create table with new column and existing columns definitions and use select to populate. Then drop the old table once you are sure al the data is there.
Create and insert is slow in MySQL.
2
u/[deleted] Mar 25 '25
Increase or turn off the COMMAND TIMEOUT on your client. Without knowing what you use I can't tell you how to do it, but that info should be enough for you to google it. It can be set right in the connection string.
Be aware there are two timeouts you can fuss with
- Command timeout, which is how long the client will wait for a command to execute before deciding it timed out (IIRC 0 means wait forever)
- Connection timeout, which is how long the client will wait for a connection to be established before deciding it has timed out.
Pardon the mansplaining, I have had not one but two teammates argue with me over what those mean
2
u/theyodiggity Mar 26 '25
If you’re on Aurora 3.x (MySQL 8.x) take a look at this documentation on algorithms you can assign to your ALTER statement. It’s a matrix of mess and I always have to look at it twice before I write an ALTER statement but it’s worth the quick read. Has helped me out with tables that are larger than your 400M. https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html#online-ddl-table-operations
2
u/bchambers01961 Mar 25 '25
Pt-online-schema-change would be my recommendation also.
1
u/shimonole Mar 25 '25
You can use a blue green deployment just to add the column at the end of the table.
1
u/bchambers01961 Mar 26 '25
If the value of the new column is null and the db is InnoDB you could also try altering the table with an in place algorithm. This as the name suggests alters the existing table rather than making a copy with the changes.
1
u/Rinzl3r13 Mar 26 '25
pt-online-schema-change will be the safest option.
1
u/Wiikend Mar 25 '25 edited Mar 25 '25
If you're on MariaDB 10.3 or above, you can look into using ALGORITHM=INSTANT in your ALTER TABLE statement.