1

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 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5
  • Show us the CREATE TABLE Commented Oct 3, 2016 at 13:36
  • Why would it? If the indexes do not reference that column, nothing would change about them. Commented Oct 3, 2016 at 14:39
  • SHOW CREATE TABLE shows more info that DESCRIBE. 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. Commented Oct 4, 2016 at 9:58
  • @ypercubeᵀᴹ show table status from <dbName> gives me that info of average row size. I have also mentioned the indexes in the table now
    – Peter
    Commented Oct 4, 2016 at 9:59
  • @ypercubeᵀᴹ Added show create table as well
    – Peter
    Commented Oct 4, 2016 at 11:10

1 Answer 1

2

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).

4
  • I have updated the question with more details
    – Peter
    Commented Oct 4, 2016 at 9:52
  • If field3 is just a 0/1 flag, make it TINYINT UNSIGNED -- that will save about 1229128263 bytes in the table. How big can field1 and field2 get? Perhaps not bigger than 4 billion, in which case INT UNSIGNED would shrink the table almost in half. Is this a "many-to-many" mapping table?
    – Rick James
    Commented Oct 4, 2016 at 17:37
  • they can be bigger then 4 billion , think of them something similar to mobile numbers and Yes its many to many mapping table . Field 3 cannot be a boolean
    – Peter
    Commented Oct 5, 2016 at 6:49
  • If they can have leading zeros, you will need VARCHAR(...) CHARACTER SET ascii.
    – Rick James
    Commented Oct 5, 2016 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.