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:
SELECT * FROM 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.
Recommended topics, DCL Commands in SQL and Tcl Commands in SQL