Mysql Index...
Indexes are used to retrieve data from the database more quickly than otherwise.
1. CREATE INDEX : The CREATE INDEX statement is used to create indexes in tables.
Ex :
CREATE INDEX idx_lastname
ON Persons (LastName);
Examples of the create index :
1: Create an index with the new table.
Execute the below query to create the table and index column. Here, the index assigns to multiple columns. You can apply index on either single column or multiple columns.
mysql> create table index_table(
-> roll_number INT NOT NULL PRIMARY KEY,
-> number INT NOT NULL,
-> mark INT NOT NULL,
-> name varchar(45),
-> INDEX (roll_number, number));
Execute the following query to get the index of the table.
mysql> show index from index_table;
2: Create index with an existing table
Execute the below query to create an index column in an existing table. Here, the index assigns to a single column. You can apply index on either single column or multiple columns.
mysql> create index mark on index_table(mark);
2. CREATE UNIQUE INDEX : Creates a unique index on a table. Duplicate values are not allowed.
Ex :
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2);
unique index
1) Example: Create a unique index with a new table.
Execute the below query to create a table with the unique index column. Here, unique index assign to multiple column. You can also apply this index on either single column or multiple columns.
mysql> create table index_table(
-> roll_number INT NOT NULL PRIMARY KEY,
-> number INT NOT NULL,
-> mark INT NOT NULL,
-> name varchar(45),
-> UNIQUE INDEX (roll_number, number));
Execute the following query to get the index of the table.
mysql> show index from index_table;
2) Example: Create unique index with existing table
Execute the below query to create a unique index column in an existing table. Here, the index assigns to a single column. You can apply this index on either a single column or multiple columns.
mysql> create unique index mark on index_table(mark);
Execute the following query to get the index of the table.
mysql> show index from index_table;
3. DROP INDEX : The DROP INDEX statement is used to delete an index in a table.
ALTER TABLE table_name
DROP INDEX index_name;
delete index
1) Example: The basic delete index example shows below.
Execute the below query to delete unwanted indexes from the table. The index name and table name requires to delete the index.
mysql> drop index mark on index_table;
Execute the below query to see the available index from the table.
mysql> show index from index_table;
MySQL Drop Index
2) Example: The basic delete index with database example shows below.
Execute the following query to delete the index from the available table. Here you will use the "alter" keyword with the "drop index" statement.
mysql> alter table index_table DROP index mark;
OUTPUT
Execute the below query to see the available index from the table.
mysql> show index from index_table;
Storage index and index type :
- MySQL creates a B-Tree index type by default. This index type supports all storage engines.
- If you want to change the index type, then you need to specify the storage engine.
- MySQL table specifies storage engine and index type as per application requirement. The following table shows the storage engine and its index type.
-------------------------------------------------------------------------------------------------------------------
Storage Engine MySQL Index Type
-------------------------------------------------------------------------------------------------------------------
InnoDB storage engine :- This storage engine supports to B-Tree index type.
MyISAM storage engine :- This engine allows only B-Tree index type.
MEMORY/HEAP :- These storage engines allow the HASH index and B-Tree index.
-------------------------------------------------------------------------------------------------------------------