Is there a performance penalty in MySQL for inserting a column?

In a previous tip, I talked about potential performance issues with inserting a column at a specific position in an existing table.

That tip ended with a side point about changes in MySQL 8.0 that could help avoid performance issues, even on large tables. It bears some further explanation so you really understand what is going on. Buckle up, this will get a bit geeky.

Let's walk through an abbreviated, recent history of how MySQL handles adding columns to a table, and the different performance impacts for each:

1. In MySQL 5.6, if you altered a table definition by adding a new column, it would internally copy the table row by row with the new definition. While this didn't lock the table, it was still an expensive operation on large tables and probably not something you'd want to happen unexpectedly.

2. MySQL 8.0.12 added a new INSTANT algorithm which would allow certain types of definition changes to happen without this copy behavior and the related performance penalties. But only certain operations were supported. For example, you could modify an existing column or add a column to the end of the table, but if you tried to insert a column in a specific location, it fell back to the row-by-row copy approach.

3. MySQL 8.0.29 went a step further, and now lets you instantly add columns even in a specific position, not just at the end of the table. Progress!

This is great, but there is still one gotcha to be aware of. These instant operations create a new "row version" for the table, and MySQL only supports a maximum of 64 row versions on a table before forcing the older algorithm, which requires a full row-by-row copy and rebuild of the table.

In a future tip, I'll share some strategies for dealing with this limitation, and how to prevent a surprise table rebuild when deploying your application and running migrations in production.

Also, while I've covered some specific highlights regarding column insertion, there is a ton of nuance with regard to all the different operations, like modifying indexes, dropping columns, and so on. If you'd like to learn more, you can go very deep on this topic by looking at the MySQL docs regarding online DDL operations.

Hope this helps,

Joel

P.S. Don't live with poor application performance. Get in touch to see how we can make your Laravel app fast.