SQL index
What is the Index in SQL and need for it?
A data structure that enhances the speed of data retrieval operations.
Confusing right!!! Make it simple with an example of a library.
Let’s say you are in a huge library and you want to find a book with a specific topic like “Machine Learning”. With no help, you’d have to go through every single book in the library, which would take a long time.
In a library, there is a catalog that lists all the books and what they’re about. This catalog acts as an index, by using this catalog you can find the right book much faster. You just need to look up for “Machine Learning” in the catalog, and it tells you which books are related to that topic. So now it is easier for you to find a specific book from the library instead of searching through the entire library.
In a similar way, index works as a catalog for a database table. When there is lots of information in the table, then it would take a long time to find specific information. But if you have an index on the table column, then it’s like making a catalog for that column.
SQL indexing is a powerful technique that can significantly enhance the performance of your relational database management system (RDBMS).
In a relational database, data is stored in tables. Retrieving data from these tables can become slow as the amount of data grows. SQL indexing is a way to optimize data retrieval by creating a data structure that enhances query performance. Think of an index as an efficient reference to the data, similar to the index at the end of a book that helps you find specific topics quickly.
For an example, you have a table of user’s information which stores information like user’s name, age, address, etc. Now you want to find information about user name with “Mike” and you don’t have an index on this column. Then the database would have to go through all the names one by one to find “Mike.” But if we create an index on the “name” column, it’s like making a catalog of all the names in alphabetical order, so now we have index on “name” column and we are searching for “Mike” then it would be easy to find when names are already sorted in alphabetical order.
Benefits of Indexes:
Faster Queries: Speeds up SELECT and JOIN operations.
Lower Disk I/O: Reduces the load on your database by limiting the amount of data scanned.
Better Performance on Large Tables: Essential when working with millions of records.
So, now you have a basic understanding that what is index, and why we need it. Now let’s jump to type of indexes.
Types of Indexes
There are several types of indexes, lets take a look with syntax .
· Clustered Index :- A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key . Means It determines the physical order of records in the table. A table can have only one Clustered Index, and it is often created on the primary key column.When a table has a clustered index, the data rows are stored on the disk in the same order as the clustered index.
CREATE CLUSTERED INDEX index_name ON table_name (column1, column2, ...);
· Non-Clustered Index :- A non-clustered index is a separate data structure from the table and contains a copy of selected columns in a sorted format.It allows for quick access to data base on the indexed columns without affecting the physical order of the data.We can create multiple non-clustered indexes on a table.
CREATE NONCLUSTERED INDEX index_name ON table_name (column1, column2, ...);
Unique Index :- A unique index enforces the uniqueness of values in the column. Often used on columns representing primary keys or other unique identifiers . It ensures that no two rows in the table can have the same value in the indexed column.
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
· Filtered Index / Partial Index:-When we create an index on a subset of rows of a table or on rows with specified condition then it is called Filtered Index.In some databases, Filtered Index and Partial Index have the same meaning, but some have slightly different meaning.
CREATE INDEX index_name
ON table_name (column1, column2, ...)
WHERE filter_condition;
· Covering Index :-When an index includes all the columns required fulfilling a query, called Covering Index.For example, we have created a non-clustered index on 3 columns, and we are retrieving these only column then this non-clustered column called covering index.Covering index eliminates the need to access the table.
Column Store Index :-This index used when there is a column-store data storage system. Basically, it is used for retrieving and querying large data warehouse tables. This index uses column-store data storage rather than row-oriented data storage.
CREATE [ CLUSTERED | NONCLUSTERED ] COLUMNSTORE INDEX index_name
ON table_name;
· Spatial Index :-A spatial index allows you to index a spatial column. A spatial column is a table column that contains data of a spatial data type, similar as figure or terrain, geography.
CREATE SPATIAL INDEX index_name
ON table_name (geometry_column)
USING GEOMETRY_AUTO_GRID;
How Does SQL Indexing Work?
An index is essentially a data structure that stores a subset of the data in a table but organized in a way that allows for faster data retrieval. Here’s how it works:
Index Creation: When you create an index, the database system builds a data structure that contains a sorted copy of the indexed column(s). This data structure is typically a B-tree (Balanced Tree).
Query Optimization: When you execute a query that involves the indexed column(s), the database engine uses the index to locate the rows that match your query conditions. This significantly reduces the number of rows that must be scanned.
Faster Retrieval: Since the index provides a map to the actual data, it speeds up data retrieval, making your queries run much faster.
Example: Using SQL Indexes
Let’s consider a simple example of a database table containing information about employees, and we want to retrieve employees by their last names.
-- Create a table for employees
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
-- Insert some data
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Robert', 'Johnson');
-- Create an index on the last_name column
CREATE INDEX idx_last_name ON employees (last_name);
Now, when we want to retrieve employees with the last name ‘Smith,’ the query will benefit from the index we created.
-- Query to retrieve employees by last name
SELECT * FROM employees WHERE last_name = 'Smith';
The database engine will use the idx_last_name index to quickly find the rows where the last name is 'Smith,' resulting in improved query performance.
SQL indexing is a vital tool in your database optimization toolkit. By understanding the types of indexes and how they work, you can boost the performance of your SQL queries and ensure that your applications run smoothly, even with large datasets. Keep in mind that while indexes improve read performance, they can also impact write performance, so use them judiciously and consider the specific needs of your application.
