How to CREATE & DROP INDEX in MySQL Database Table

DATABASE INDEX is used to find, sort and group the data in a fast and efficient way. These indexes can be created using one or more columns and creating index to a column generates an extra hidden column holding the same data sorted with a handle to the original data. Hence it allows the database application to find the data fast without reading the whole table. Today we’ll see how to create and drop index in mysql database table.

Don’t Miss: How to Read HTTP Headers in PHP

Create Index in MySQL Database Table

To create index in mysql table we should use CREATE INDEX statement like this,

CREATE INDEX bk_index ON books (book_name);

It creates an index with the name ‘bk_index’ for the field (column) ‘book_name’ in the table ‘books’.

Create Index for Multiple Columns

We can also create index for a combination of two or more fields by listing the field names separated by comma inside the parenthesis like this,

CREATE INDEX bk_index ON books (book_name, author_name);

Create Unique Index in MySQL Table

Index allows duplicate values for a column, but if you want to ensure no two rows have the same index value then using the UNIQUE INDEX is the solution.

CREATE UNIQUE INDEX user_index ON users (email_id);

Drop Index in MySQL Database Table

You can also drop an existing index for a mysql table. To delete the index we should use ALTER command with DROP clause like this,

ALTER TABLE books DROP INDEX (bk_index);

Where ‘books’ is the table name and ‘bk_index’ is the index name.

Similarly using ADD clause with the ALTER command, we can create new index like this,

ALTER TABLE books ADD INDEX (bk_index);

If you don’t specify name for an index mysql will assign a default name for the index.

If you don’t know the index name to drop then using the SHOW INDEX statement will list all the indexes associated with a particular table.

SHOW INDEX FROM table_name;

Also Read:

• Backup & Restore MySQL Database Easily using Command Line
• How to Get User IP Address in CodeIgniter Framework

I hope now you have better understanding of creating and dropping index on mysql database table.