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 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
CREATE TABLE
SHOW CREATE TABLE
shows more info thatDESCRIBE
. Please edit the question and add that instead. How did you conclude that average size is 97 bytes? Seems a bit much for these 5 columns.