Database indexes in SQL, allows the developer to quickly retrieve the data from a database. The searching process in a database can be accelerated using database indexes. A database index is a copy of key values stored in a table. If database index is not used, then every query will scan the entire table to retrieve the required data. A database index is similar to the index page used in a book.
Types of Database indexes
Creating a index should be a planned process because indexes will consume space from database. Creating a index with duplicated values will increase the search time which defeats the purpose of creating database indexes. There are three types of data structures used to create database indexes they are:
- B+ trees
- Balanced trees
- Hashes
It is recommended to update the index of the database immediately after a update in the table. Regularly updated index will efficiently handle the searches in the database. Indexes won’t return any values but locate the records present in a table.
- Read: What is Index in SQL?
The architecture of database index is classified into two divisions:
- Clustered index: The datas saved using clustered indexes are based on the row in which the particular data is stored. The clustered index works efficiently even if rows and columns present in a table are altered frequently. Only one clustered index is allowed for a table.
- Non Clustered Index: Nonclustered index arranges data in a random manner but they are logically ordered using specified index. The order of nonclustered index won’t resemble the order of data stored in a table. Nonclustered indexes are mostly preferred in tables in which WHERE and JOIN statements are used frequently.
How indexes are created in SQL?
The following SQL can be used if you want to create a index for student table
CREATE INDEX name_index
ON Student (Student_Name)
If multiple columns are present in a table, following SQL can be used.
CREATE INDEX name_index
ON Student(Student_Name, Student_Age)
How performance of a database is improved using indexes?
Indexes store the common values used in a database. If the common values are sorted it will further enhance the performance of a database. Let us consider that we have a table which contains details about students. If we search for a student named “Jordi Alba” in the column student_name, the table without index will search the entire database to return the result about the search query. If indexes are used to sort the student name alphabetically, the database will start with index starting with “J”. Combining search query with various index combinations will yield better results.
Important points to follow while creating a index:
DBMS will automatically maintain all the indexes created in it. Indexes will be updated automatically according to the changes made in the table.
- It is best to create an index immediately after creating a table. Database developers are advised anticipate the use of an index while creating a table. If multiple search queries are required for a table, an index for every possibility is recommended.
- Don’t create an index for data which are not used because creating will slow down the performance of a database. Creating indexes which are not needed will add as a junk data in your database.
- Creating indexes for tables with large rows is always recommended.
- Creating indexes to the keys which are used rare is recommended just before the use of keys. Removing these indexes after its use is highly recommended because these indexes may slow down the performance of the database.
- Creating many indexes to a table which requires frequent updates is not recommended because frequent updates in indexes corresponding to the table update is required.
- If the size of index key is large, it will consume more disk space so index keys which are smaller in size are advocated.
- The use of non clustered index is mostly recommended while using WHERE and JOIN statements.
- Narrow index columns are recommended to easily equate the index keys with I/O operations.