Facebook Twitter Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    You are at:Home»Databases»MariaDB»How to Create, List or Drop Indexes on MySQL Table

    How to Create, List or Drop Indexes on MySQL Table

    By RahulApril 3, 20162 Mins Read

    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.

    Advertisement

    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;
    

    Index mysql index
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    Installing MySQL 8.0 on Amazon Linux 2

    How To Install MySQL 8 on Amazon Linux 2

    (Resolved) Unknown collation: utf8mb4_unicode_520_ci

    How To Install phpMyAdmin on Ubuntu 22.04

    How To Install phpMyAdmin on Ubuntu 22.04

    Add A Comment

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • Error: EACCES: permission denied, scandir (Resolved)
    • How To Install Python 3.11 on Ubuntu 22.04 / 20.04
    • How to Install Python 3.11 on Amazon Linux 2
    • An Introduction to the “./configure” Command: Compiling Source Code in Linux
    • How to Install PHP 8.x on Pop!_OS
    Facebook Twitter Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.