Skip to content

How to Create, List or Drop Indexes on MySQL Table

This tutorial will help you to Create index, List index or Drop Indexes on a MySQL database Table.

Category
Databases
Covers
MySQL
Versions & support
About MySQL →
On this page

Indexes are very useful to improve search queries on database tables. For example you are searching for particular record in a database table having millions of records. You will find that search queries taking very less time on tables having indexes enables.

CREATE INDEX:-

This will creates index on mysql table. This will allow duplicate values also.

CREATE INDEX index_name ON table_name (COLUMN_NAME1, COLUMN_NAME2, ...)

CREATE UNIQUE INDEX:-

This will creates index on mysql table. With uses of UNIQUE keyword this will not allow duplicate values.

CREATE UNIQUE INDEX index_name ON table_name (COLUMN_NAME1, COLUMN_NAME2, ...)

1. CREATE INDEX Examples:-

For this example I have a table named Users with thousands of records in mysql database. The table structure is as following in screenshot.

mysql-index-1

Now use following command to create index named Users_Idx1 on table Users based on column username.

mysql> CREATE INDEX Users_Idx1 ON Users (username);

Use UNIQUE keyword with the query to create index with unique records.

mysql> CREATE UNIQUE INDEX Users_Idx1 ON Users (username);

2. SHOW INDEX Examples:-

Use following sql command to list all indexes for a mysql table. The below query will delete Users_Idx1 index from Users table in current database.

mysql> SHOW INDEX FROM Users;

mysql-index-2

3. DELETE INDEX Examples:-

Use following sql command to delete index on mysql table.

mysql>  ALTER TABLE Users DROP INDEX Users_Idx1;

Rahul

Rahul Kumar is the founder and chief editor of TecAdmin.net. A Red Hat Certified Engineer (RHCE), he has worked in IT since 2009, with hands-on experience in Linux system administration, DevOps, web hosting, cloud platforms and automation. He started TecAdmin in 2013 to share practical, step-by-step guides, and leads its editorial work with one aim: making technical learning clear and useful for sysadmins, developers and beginners alike.

Leave a Reply

Your email address will not be published. Required fields are marked *


Back to top