Mysql – Adding a New column in thesql , Impact on index size

MySQL

I have to add a new column smallint(16) with default 32767.

I already have 2 indexes on this table , one is a composite index on 2 columns and another one is a secondary index on one of the column in composite index.

What all will be impacted on adding this new column ?

data size(will increase as it has to back fill)

Will it impact existing Index size too?

This is a very large table with "1229128263" rows and average row length of 97 bytes

There is a clustered index on field1 and field2 which is also primary key.
Also there is a separate index on field2

Mysql version is 5.6.19

 describe tableName;
+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field     | Type        | Null | Key | Default           | Extra                       |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| field1   | bigint(20)  | NO   | PRI | NULL              |                             |
| field2   | bigint(20)  | NO   | PRI | NULL              |                             |
| field3    | smallint(6) | YES  |     | 1                 |                             |
| timestamp | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| field4   | smallint(6) | YES  |     | 32767             |                             |
+-----------+-------------+------+-----+-------------------+-----------------------------+

show index from tableName;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tableName |          0 | PRIMARY  |            1 | field1     | A         |   409708217 |     NULL | NULL   |      | BTREE      |         |               |
| tableName |          0 | PRIMARY  |            2 | field2     | A         |  1229124651 |     NULL | NULL   |      | BTREE      |         |               |
| tableName |          1 | field2  |            1 | field2     | A         |   307281162 |     NULL | NULL   |      | BTREE      |         |               |
    +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


show create table tableName;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                             |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tableName | CREATE TABLE `tableName ` (
  `field1` bigint(20) NOT NULL,
  `field2` bigint(20) NOT NULL,
  `field3` smallint(6) DEFAULT '1',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`field1`,`field2`),
  KEY `field2` (`field2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Best Answer

Short answer: Add the column and don't worry about anything.

Long answer:

Adding a SMALLINT will add 2 bytes to each row, plus some overhead.

The (16) means nothing.

As for the indexes -- Please provide SHOW CREATE TABLE; your description is too imprecise.

Existing indexes may or may not grow or shrink in size. This is because the indexes may or many not be rebuilt by the process that adds the new column. That depends on which version of MySQL you are running.

"Impact?" - If it is a huge table, the table may be unavailable during the ALTER.

Perhaps you want SMALLINT UNSIGNED and have a default of 65535?

Perhaps you could use NULL as the default (with some code changes).

Related Question