How Index Works in SQL?
FREE
Overview
Index is a table that helps in faster query results and quick data retrieval from the database. Index tables use indexing, a technique that uses data structures to optimize the searching time of a database query. Indexing makes database performance better. It also consumes lesser space in the main memory.
What is an INDEX?
Index usually consists of two columns which are a key-value pair. The two columns of the index table (i.e., the key-value pair) contain copies of selected columns of the tabular data of the database.
Generally, we store the selected Primary or Candidate keys in a sorted manner so that we can reduce the overall query time or search time (from linear search to binary search).
Data Reference contains a set of pointers that holds the address of the disk block. The pointed disk block contains the actual data referred to by the Search Key. Data Reference is also called Block Pointer because it uses block-based addressing.
Indexing is used to quickly retrieve particular data from the database. Indexing reduces the number of disks required to access a particular data by internally creating an index table. Indexing is achieved by creating Index-table or Index.
How to create An Index?
The index can be created very easily using SQL commands.
Syntax:
Suppose we have a database of students. The student table has columns: student ID, Name, Physics (marks in Physics), and Chemistry (marks in Chemistry).
Student table:
| ID | Name | Physics | Chemistry |
|---|---|---|---|
| 1 | Aman | 86 | 92 |
| 2 | Sushant | 91 | 91 |
| 3 | Saumya | 98 | 98 |
| 4 | Aditya | 93 | 88 |
| 5 | Nilesh | 87 | 83 |
Let us create an index on the student table using the ID column.
Let us learn some of the advantages of using indexing on the tables of the database.
Advantages of Creating Index Table or Indexing:
- Indexing helps in faster query results or quick data retrieval.
- Indexing helps in faster sorting and grouping of records
- Some Indexing uses sorted and unique keys, which helps to retrieve sorted queries even faster.
- Index tables are smaller in size, so they require lesser memory.
- As Index tables are smaller in size, they are stored in the main memory.
- Since CPU speed and secondary memory speed have a large difference, the CPU uses this main memory index table to bridge the gap of speeds.
- Indexing helps in better CPU utilization and better performance.
Refer to the image below to see how index works in SQL and how indexing divides the large database into smaller sub-contents.
How does Index Work?
Now that we know index and its advantages, let us now know how index works in SQL. Generally, the database takes the column provided with the CREATE INDEX command and sorts the column values using a special data structure called B-trees.
B-tree is a type of tree data structure that contains 2 things: Index Key and its corresponding disk address. Index Key refers to a certain disk address, and that disk further contains rows or tuples of data. Using B-tree, we can achieve fast searches and fast retrievals. Also, the disk space used is low.
Since the data structure, B_tree, is sorted in order, it makes our search more efficient. So, this is how the index works in SQL. Now let us take a few examples to visualize how index works in SQL.
Example - We want to store the below three-column table in the database.
| Name | Marks | Age |
|---|---|---|
| Jone | 5 | 28 |
| Alex | 32 | 45 |
| Tom | 37 | 23 |
| Ron | 87 | 13 |
| Mark | 20 | 48 |
| Bob | 89 | 32 |
The B-tree representation will be like this:
In the index table, the left column contains the index key, which holds the reference to a particular table row.
Let us take an example to understand how the index works in SQL. Suppose we have the database of a college which stores the details of students. We will store the name, age, CGPA, roll number, and section of students.
The student table is shown below:
Student table
| Roll_No | CGPA | Name | Section |
|---|---|---|---|
| 1 | 9.21 | Aman | A |
| 2 | 8.86 | Aditya | A |
| 13 | 9.92 | Sushant | C |
| 24 | 9.71 | Mohit | - |
| 5 | 8.56 | Saumya | C |
Suppose the index of the above table is roll_index. Now, a search using the index field can increase performance. The index uses B-tree, so this allows SQL for a binary search of the index with an average of log(n) accesses.
:::{.highlight} Note: We should use indexing when the table contains numerous entries. For a smaller table, the time taken by indexing is somewhat similar to a query without an index table.
:::
How Does the Database Know What Other Fields in the Table to Return?
As we have discussed above, the index table has two columns. The first column stores the key that points (or references) to a particular location of information. Using the student table example, we can visualize that a certain roll number index will point to a record of the table. The record can contain numerous columns or attributes. Hence, using an index, the database can know about the rest of the attributes of the table.
Conclusion
- Index is a table that helps in faster query results and quick data retrieval from the database. Index tables use indexing, a technique that uses data structures to optimize the searching time of a database query.
- Indexing makes better database performance. It also consumes lesser space in the main memory.
- The database takes the column provided with the CREATE INDEX command and sorts the column values using a special data structure B-trees.
- Index tables are smaller in size, so they require lesser memory. As Index tables are smaller in size, they are stored in the main memory.
- Since CPU speed and secondary memory speed have a large difference, the CPU uses this main memory index table to bridge the gap of speeds.
- Indexing helps in better CPU utilization and better performance.