I am using MySQL 5.7.17
When I changed the data type of a large test table from varchar(45) to varchar(50), it took only less than a second to complete.
I couldn't find a good document explaining why this happened so fast. Please help.
(We are going to change the datatype from varchar(45) to varchar(50) on our huge production table, and it will be helpful for us to calculate the downtime needed for this.)
A table has a "definition" (the CREATE TABLE...) and it has the data and the indexes.
If an ALTER can be performed without changing the way the data is stored, then it can (in most cases, and in recent versions), do it by changing the definition without touching the actual data.
So, let's look at how a VARCHAR(N) column is stored:
For short definitions (that is, for small N):
* 1-byte length field
* a variable number of bytes to hold the actual _characters_
For bigger columns (N=256 certainly qualifies):
* 2-byte length field
* a variable number of bytes to hold the actual characters
So, switching from N=40 to N=50 would not need to touch the data, but N=45 to N=266 would, I think, require rewriting all the data.
(I am being a bit vague on N because a "character" can take more than one "byte", depending on the COLLATION.)
Perhaps the slowest ALTER is if you change the PRIMARY KEY. That requires reshuffling the data because it is ordered by the PK and requires rebuilding all the secondary indexes since they include a copy of the PK.
In the middle of the spectrum are some INDEX operations. DROP INDEX is pretty cheap -- remove from the definition and then clean up the space consumed by it. ADD INDEX is more costly because it needs to populate a new BTree to hold the index. Meanwhile, the data and any other secondary indexes can remain untouched. The code, however, is necessarily tricky -- what if rows are being modified while the index is being built.