Indexes

Jainish Patel
Last Updated: May 13, 2022

Introduction

Have you ever wondered how google searches millions of search results in just a fraction of a second? To answer this question, a word search "programming" was searched on google. The below image shows the result. 

In 0.65 seconds, the google search engine finds 3.81 trillion searches relevant to the word we had searched. After getting amazed by the Google search engine's performance, your manager gives you the same task to reduce the time taken by the company's database. Which consists of 1000000 entries of data only, but it still takes 4-5 seconds to get the result of the simple search queries on the database.

 

To solve this task, let’s learn an amazing concept of Indexes in SQL.

Indexes

This article will discuss Indexes in SQL, the need for Indexes in SQL, and queries related to Indexes.

What is the need of Indexes?

By the word Indexes, the first thought is the Index section of a book. Whatever book you pick, the index section is always available so that the reader can quickly figure out what he/she is looking for. In the same way, the Indexes work in SQL.

A SQL index is a type of index that allows you to access data from a database quickly. Indexing a table or view is undoubtedly one of the most effective techniques to increase query and application performance.

A SQL index is a quick lookup table for locating often searched records. An index is a data structure that is small, fast, and optimized for speedy lookups. It's great for linking relational tables and searching huge databases.

Indexes in SQL

An Indexes helps to speed up SELECT queries and WHERE clauses. It slows down data input in UPDATE and INSERT statements. There is no influence on the data when indexes are created or dropped.

The CREATE index is used to create an index. It allows you to name the index, provide the table and the columns to index, and define whether the index should be in ascending or descending order.

Note: Creating Indexes for a small database table is not efficient, the database should be big enough that it can create Indexes.

Syntax

CREATE INDEX IndexName ON TableName;

Indexes are of various Types

Single column Indexes

A single column index is created based on only 1 table column.

Syntax

CREATE INDEX IndexName ON TableName (ColumnName);

Unique Indexes

Unique indexes are used not only for performance but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table.

Syntax

CREATE UNIQUE INDEX IndexName ON TableName (ColumnName);

Composite Indexes

A composite index is an index on two or more table columns.

Syntax

CREATE INDEX IndexName ON TableName (Column1, Column2);

Consider the columns you may frequently utilize as filter criteria in a query's WHERE clause when deciding whether to create a single index or a composite index.

A single-column index is the best option when only one column is used. The composite index is the best option if two or more columns are regularly utilized as filters in the WHERE clause.

Various operations on Indexes

Removing an Index: 

To remove an index from the data block by using the DROP INDEX command. 

Syntax 

DROP INDEX index;

To drop an index from the database table it is compulsory to get permission.

Altering an Index: 

To modify an existing table's index by recreating or reorganizing the index. 

Syntax 

ALTER INDEX IndexName ON TableName REBUILD

 

Confirming Indexes :

Users can check the different indexes present in a table given by the user or the server itself and their uniqueness. 

Syntax: 

SELECTFROM UserIndexes;

 

Renaming an index : 

You can use the system stored procedure sp_rename to rename any index in the database. 

Syntax: 

ALTER INDEX OldTableName RENAME TO NewTableName;

 

Let’s solve some numerical based on Indexes.

Consider a hard disk in which block size is equal to 1000 Bytes, each block records if of size 250 Bytes. If the total number of records is 10000, the data is entered in a Hard Disk without any order(Unordered).

 

1. The number of records we can put in each block?

To get the records per block we need to divide the total capacity of each block by the size of each record.

2. The number of blocks required to store a total number of records?

To get the number of blocks required we need to divide the total entries by the capacity of each block.

3. What is the average time complexity(I/O cost) of searching a record from a Hard Disk?

 

BEST case: The record for which the user is looking is present at the first block. So time taken will be O(1).

WORST case: The record for which the user is looking is present at the last block. So the time taken will be O(N).

Average Case: The record we are for which the user is looking for is present at the middle of the blocks or half of the total number of blocks. So the time taken will be O(N/2).

NOTE: We are performing Linear Search as the data is not sorted.

If it is mentioned in the question that the data is in sorted order, then we can apply Binary search and improve worst-case complexity to O(logN).

 

The question which we search earlier does not contain an Index table. Now at look at another question where the index table is present.

Consider a hard disk in which block size is equal to 1000 Bytes, each block records if of size 250 Bytes. If the total number of records is 10000, the data is entered in a Hard Disk without any order(Unordered). What is the average time complexity(I/O cost) of searching a record from an Index Table if the Index table key is equal to 20 Bytes (10 key + 10 Pointer)?

 

The key present in the index table represents the title or name. If we consider a book index section then it can to refer as topic name, and the pointer can be referred as the page number.

 

4. Each block in the Index table contains?

Index Table can store both total numbers of entries is known as Dense which can be used when data is unordered. Or a Total number of blocks is known as Sparse, which can be used when data is in sorted order.

 

As per the current question, the data is stored unordered, so Dense can be used.

 

Dense 

Number of searches required to find a record

One is added to the index table search’s answer because we also need to find the entry.

 

Sparse (if the data is in sorted order)

Number of searches required to find a record

One is added to the answer of the index table search because we need to find the entry also from the block we get from the Index table.

 

Frequently Asked Questions

1. What are the conditions to consider before creating an INDEX?

Below are the conditions where indexes can be created: 

  • A column should contain a wide range of values.
  • A column does not have many null values. 
  • One or more columns will frequently be used together in a where clause or a join condition. 

 

2. What are the conditions to where the INDEXES should be avoided?

Below are the conditions where indexes can be avoided:

  • The table with less number of entries.
  • The column is not often used as a condition in the query. 
  • The column is updated frequently.

 

3. What are the types of Indexes available in SQL?

Single-column, Unique, Composite are the types of Indexes available in SQL.

 

4. If there is a database table with 10000 entries is it good to create indexes?

Creating an index table will not improve the performance of the search. So, better not to create it. 

Key Takeaways 

This article discusses how to use the Indexes using various examples and write SQL queries. 

Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Furthermore, to get hang on and practice different types of questions on SQL queries, you can visit Database Management Systems FREE Guided path. If you want to learn DBMS from an industry expert, check out a fantastic course on DBMS

Was this article helpful ?
0 upvotes

Comments

No comments yet

Be the first to share what you think