Database Index
An index is a structure defined data on a column of table (or more) and allows quickly locate the rows of the table based on their content in the indexed column and allows retrieve the rows of the table sorted by the same column.
Types of indices
Simple and compound index
A simple index is defined on a single column of the table while a compound index is made up of several columns of the same table (table on which the index is defined). When an index is defined on a column , the records that are retrieved using the index will appear ordered by the indexed field. If you define an index made up of columns col1 and col2 , the rows that are retrieved using that index will appear ordered by the values of col1 and all the rows that have the same value of col1 ‘ will be ordered in turn by the values contained in col2, function the same as the ORDER BY clause . For example, if we define a composite index based on the columns (province, town), the rows that are retrieved using this index will appear ordered by province and within the same province by town.
Clustered and non-clustered index
The term clustered index should not be confused with a composite index, the meaning is totally different. A clustered index ( CLUSTERED ) is an index in which the logical order of the key values determines the physical order of the corresponding rows in the table . The bottom level, or leaf , of a clustered index contains the actual rows of data from the table. A table or viewallows only one clustered index at a time. The existing nonclustered indexes on the tables are rebuilt when creating a clustered index, so it is a good idea to create the clustered index before creating the nonclustered indexes. A nonclustered index specifies the logical ordering of the table. With a nonclustered index, the physical order of the data rows is independent of the indexed order.
Unique index
Unique index is one in which two rows are not allowed to have the same value in the key column of the index. In other words, it does not allow duplicate values.
Advantages and disadvantages of indexes
Advantage
- Using indexes can improve queryperformance , since the data necessary to satisfy the query’s needs exists in the index itself. That is, only the index pages and not the data pages of the table or clustered index are needed to retrieve the requested data; therefore, the overall I / O on the disk is reduced . For example, a query on columns a and b of a table that has a composite index created on columns a , b, and c can retrieve the specified data from the index itself.
- Indexes on viewscan significantly improve performance if the view contains aggregations, table joins, or a mix of aggregations and joins.
Drawbacks
- The tablesused to store the indexes take up space.
- Indexes consume resourcessince each time an update, insert or delete operation is performed on the indexed table, all the index tables defined on it have to be updated (in the update, it is only necessary to update the indexes defined on columns being updated). For these reasons, it is not a good idea to define indices indiscriminately.
Considerations to take into account
- Avoid creating too many indexes on tablesthat are updated very frequently and try to define them with as few columns as
- It is convenient to use a larger number of indexes to improve the performanceof queries on tables with little update needs but with large volumes of data. A large number of indexes helps improve the performance of queries that do not modify data, such as SELECT statements , since the [[optimizer
query]] you have more indexes to choose from to determine the fastest access method .
- It is recommended to use a short key length for clustered indexes. Clustered indexes are also better if they are created on single or non- nullable columns.
- A unique index instead of a non-unique index with the same combination of columnsprovides additional information to the query optimizer and is therefore more useful.
General syntax for creating an index
CREATE INDEX “INDEX_NAME” ON “TABLE_NAME” (COLUMN_NAME) ; Let’s say we have the following table: Table Column Name Data Type First_Name char (50) Last_Name char (50) Address char (50) City char (50) Country char (25) Birth_Date datetime
If we want to create an index on both City and Country , we would enter, CREATE INDEX IDX_CUSTOMER_LAST_NAME ON Customer (Last_Name) ; There is no hard and fast rule about how to name an index. The generally accepted method is to place a prefix, such as “IDX_”, before an index name to avoid confusion with other objects in the database . It is also a good idea to provide information on which table and column (s) the index will use. Please note that the exact syntax for CREATE INDEX it may be different depending on the different databases.
