Columns that are used for filtering or joining should have indexes on them. An index is a specific data structure that organizes data in such a way that it is much faster for database management system to look up the records at query time. Indexes are great for accessing your data faster.
An index helps to speed up SELECT queries and WHERE clauses, but it slows down the data input with UPDATE and INSERT statements because indexes need to be maintained according to new data. Indexes can be created or dropped with no effect on the data.
Below is an example of how to create index:
CREATE INDEX index_name ON table_name;
We can create indexes on:
primary key (automatically created)
foreign key (some databases automatically create index on foreign keys)
column which is being used in a JOIN clause
column which is being used in a WHERE clause
Different types of indexes:
Single column index: only on one column of a table:
Syntax:
CREATE INDEX index_name ON table_name (column_name);
Multi-column index: on more than one column of the table:
Syntax:
CREATE INDEX index_name ON table_name(column1_name, column2_name)
Unique index: A unique index does not allow any duplicate values to be inserted into the table and it also results in faster performance:
Syntax:
CREATE UNIQUE INDEX index_name ON table_name (column_name);
How to DROP index?
Syntax:
DROP INDEX index_name;