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.
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;
