How to Create, List or Drop Indexes on MySQL Table

Create, List or Drop Indexes on MySQL Table

MySQL is an open-source relational database management system (RDBMS); in July 2013, it was the world's second most widely used RDBMS, and the most widely used open-source client–server model RDBMS.It is named after Michael Widenius' (who is a co-founder of MySQL) daughter, while "SQL" stands as the abbreviation for Structured Query Language. 

The MySQL development project has made its source code available under the terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now owned by Oracle Corporation. For proprietary use, several paid editions are available, and offer additional functionality.

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.

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;

3. DELETE INDEX Examples:-

Use following sql command to delete index on mysql table.

mysql>  ALTER TABLE Users DROP INDEX Users_Idx1;

www.hackthesec.co.in