SQL Indexes
-
SQL Indexes are used to make the data retrieval faster by using the pointers.
-
When looking for a record in smaller database we will get the desired output in seconds.
-
But when looking for data in a larger datasets, it will take time, index helps to get the data faster and increase the speed of the retrieving the data.
Syntax
CREATE INDEX index_name
ON table_name (column_1,column_2,....)
Single Indexes
-
Single index contain only one column.
EXAMPLE : Single Index
CREATE INDEX i_birth
on employees (birth_date);
Composite Indexes
-
Composite index contain more than one column.
EXAMPLE : Composite Index
CREATE INDEX i_name
on employees (first_name, last_name);
-
Primary key and Unique key by default becomes the indexes because it makes sense because people usually search on that column only to get the distinct values.
-
we can see the indexes for complete database by clicking on the icon on the right side of the database.
-
We can see the Indexes of particular column by clicking on the icon on the right side of the column name.
-
We can also write the below query to see the indexes.
Syntax
SHOW INDEX FROM table_name FROM database_name
-
​Creating a Indexes consumes memory spaces and resources.
-
Being a good SQL Developer, we should always look for right balance between speed, resources and memory use to perform a task.
-
So it is advisable to not use indexes for smaller datasets.
-
It will be beneficial to use indexes for larger dataset, even though it contains memory but query execution will be faster and more efficient.