Add new MySQL table columns and create indexes?

To add a new MySQL table column and index, you can use ALTER TABLE command.

The syntax is as follows

ALTER TABLE yourTableName ADD COLUMN yourColumnName dataType, ADD INDEX(yourColumnName );

To understand the above syntax, let us create a table. The query to create a table is as follows

mysql> create table AddColumnAndIndexDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(100),    -> Address varchar(200)    -> ); Query OK, 0 rows affected (0.81 sec)

Now you can check the description of table. The query is as follows −

mysql> desc AddColumnAndIndexDemo;

The following is the output

+---------+--------------+------+-----+---------+----------------+ | Field   | Type         | Null | Key | Default | Extra          | +---------+--------------+------+-----+---------+----------------+ | Id      | int(11)      | NO   | PRI | NULL    | auto_increment | | Name    | varchar(100) | YES  |     | NULL    |                | | Address | varchar(200) | YES  |     | NULL    |                | +---------+--------------+------+-----+---------+----------------+ 3 rows in set (0.03 sec)

The following is the query to add a new MySQL table column and index

mysql> alter table AddColumnAndIndexDemo add column Age int, add index(Age); Query OK, 0 rows affected (1.81 sec) Records: 0 Duplicates: 0 Warnings: 0

Check the table description once again. The query is as follows −

mysql> desc AddColumnAndIndexDemo;

The following is the output

+---------+--------------+------+-----+---------+----------------+ | Field   | Type         | Null | Key | Default | Extra          | +---------+--------------+------+-----+---------+----------------+ | Id      | int(11)      | NO   | PRI | NULL    | auto_increment | | Name    | varchar(100) | YES  |     | NULL    |                | | Address | varchar(200) | YES  |     | NULL    |                | | Age     | int(11)      | YES  | MUL | NULL    |                | +---------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)

Check the index from table using SHOW command. The query is as follows −

mysql> show index from AddColumnAndIndexDemo;

The following is the output

+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table                 | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | +-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | addcolumnandindexdemo | 0          | PRIMARY  | 1            | Id          | A         | 0           | NULL     | NULL   |      | BTREE      |         |               | YES | | addcolumnandindexdemo | 1 | Age | 1 | Age | A | 0 | NULL | NULL | YES | BTREE | | | YES | +-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ 2 rows in set (0.16 sec)