• Home
  • Ubuntu 18.04
    • Whats New?
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install Git
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us
TecAdmin
Menu
  • Home
  • Ubuntu 18.04
    • Whats New?
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install Git
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us

How to Create, List or Drop Indexes on MySQL Table

Written by Rahul, Updated on April 3, 2016

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;

Share it!
Share on Facebook
Share on Twitter
Share on LinkedIn
Share on Reddit
Share on Tumblr
Share on Whatsapp
Rahul
Rahul
Connect on Facebook Connect on Twitter

I, Rahul Kumar am the founder and chief editor of TecAdmin.net. I am a Red Hat Certified Engineer (RHCE) and working as an IT professional since 2009..

Leave a Reply Cancel reply

Popular Posts

  • How To Install Python 3.9 on Ubuntu 20.04 5
  • How To Install Python 3.9 on Ubuntu 18.04 0
  • How to Use AppImage on Linux (Beginner Guide) 2
  • How to Install Python 3.9 on CentOS/RHEL 7 & Fedora 32/31 0
  • How To Install VNC Server on Ubuntu 20.04 1
© 2013-2020 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy