Indexes are needed to improve the performance of SELECT queries. They can be defined with table creation and also created after table creation with CREATE INDEX and ALTER TABLE statements. If during optimization you find that an index is not useful then you can DROP them using DROP INDEX or ALTER TABLE statements.

Creating table with indexes

 
You can define table indexes while creating a table.

CREATE TABLE `employees`(
     `emp_id` int NOT NULL,
     `first_name` varchar(255) NOT NULL,
     `last_name` varchar(255)
     `profile_data` TEXT(5000),
     PRIMARY KEY (`emp_id`),
     INDEX `fname_idx` (`first_name`),
     FULLTEXT `profile_idx` (`profile_data`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

CREATE TABLE `employee_addresses`(
     `address_id` int NOT NULL AUTO_INCREMENT,
     `emp_id` int NOT NULL,
     `street` varchar(255) NOT NULL,
     `city` varchar(255),
     `country` varchar(100),
     `zipcode` varchar(20),
     PRIMARY KEY (`address_id`),
     CONSTRAINT `fk_emp_addresses` FOREIGN KEY `fk_emp_addresses` (`emp_id`)     REFERENCES `employees`(`emp_id`) ON DELETE CASCADE ON UPDATE CASCADE )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

CREATE TABLE `flowers`(
    `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` int NOT NULL UNIQUE COMMENT 'Name of the flower',
    `details` TEXT(500) );

CREATE INDEX statement

CREATE INDEX statement enables you to add indexes to existing tables. It can be used to create Simple, UNIQUE, FULLTEXT, SPATIAL indexes for a table. It cannot be used to create a PRIMARY KEY.

Lets look at the examples:

1. Simple Index (Simple Index allows duplicate values in a table)

CREATE INDEX `index_name` ON `table_name` (`column_name`);

2. To create a Composite Index, specify multiple columns as shown below:

CREATE INDEX `index_name` ON `table_name` (`column_name1`, `column_name2`, ...);

3. You can also specify whether the column values will be indexed in ascending order or descending order as shown below:

CREATE INDEX `index_name` ON `table_name` (column_name1 ASC, column_name2 DESC, ...);

4. You can also specify the column lengths for creating indexes:

CREATE INDEX `index_name` ON `table_name` (column_name1(length), column_name2(length) DESC, ...);

5. To create a UNIQUE Index

CREATE UNIQUE INDEX `index_name` ON `table_name` (`column_name`);

6. Similarly you can create a FULLTEXT Index

CREATE FULLTEXT INDEX `index_name` ON `table_name` (`column_name`);

Using ALTER TABLE to add indexes

1. Adding a PRIMARY KEY

ALTER TABLE `table_name` ADD PRIMARY KEY (`column_name`);
ALTER TABLE `table_name` ADD CONSTRAINT `pk_symbol` PRIMARY KEY (`column_name`);

2. Adding a UNIQUE KEY

ALTER TABLE table_name ADD UNIQUE KEY (column_name);
ALTER TABLE table_name ADD CONSTRAINT uk_symbol UNIQUE KEY index_name (column_name);

3. Adding a FULLTEXT INDEX

ALTER TABLE `table_name` ADD FULLTEXT INDEX (`column_name`);
ALTER TABLE `table_name` ADD FULLTEXT INDEX `index_name` (`column_name`);

4. Adding a FOREIGN KEY

ALTER TABLE `table_name` ADD FOREIGN KEY (`column_name`) REFERENCES `table_name_primary`(` key_name` );

ALTER TABLE `table_name` ADD CONSTRAINT `fk_symbol` FOREIGN KEY (`column_name`) `index_name` REFERENCES `table_name_primary`( `key_name` );

ALTER TABLE `table_name` ADD CONSTRAINT `fk_symbol` FOREIGN KEY (column_name1,column_name2,....) index_name REFERENCES `table_name_primary`( key_name1, key_name1, ... );

Note: In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

Deleting Indexes – Using DROP INDEX statement

1. Deleting PRIMARY KEY

DROP INDEX `PRIMARY` ON table_name;

To drop a primary key, the index name is always PRIMARY, which must be specified as a quoted identifier because PRIMARY is a reserved word.

2. To drop an index that is not a PRIMARY KEY, you must specify the index name.

DROP INDEX index_name ON table_name;

Deleting Indexes – Using ALTER TABLE statement

1. Deleting PRIMARY KEY

ALTER TABLE table_name DROP PRIMARY KEY;

2. Deleting FOREIGN KEY

ALTER TABLE table_name DROP FOREIGN KEY fk_symbol;

3. Deleting other indexes/keys

ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP KEY index_name;

Note: If you are unsure of the index name, use the following statement:

SHOW INDEX FROM table_name;