MySQL – Create Index
Indexes are data structures that allow us to add indexes to existing tables. Using this method, you are able to improve the speed at which records are retrieved from a database table. For each value of the indexed columns, an entry is created. Whenever a database table is accessed, we use it to quickly locate a record without having to search each row in the table. By using one or more columns of a table, we can create an index for efficient access to the records.
A special index named PRIMARY is automatically created when a table is created with a primary key or unique key. This index is referred to as a clustered index. Non-clustered indexes and secondary indexes are all types of indexes other than PRIMARY indexes.
We will create a table employee and perform a CREATE INDEX statement on it.
Now, let us create an index for a role column using the following statement.
CREATE INDEX role ON employee (role);
The index has been successfully created after executing the above statement. Run the following statement to see how MySQL internally executes this query.
EXPLAIN SELECT cust_id, cust_name, salary FROM employee WHERE role = 'De';