SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL what-are-single-row-and-multiple-row-subqueries">What are single row and multiple row subqueries?

How index works in SQL?

What is an index in SQL?

In SQL, an index is a database structure that provides a fast and efficient way to retrieve data from a table. It works by creating a mapping of values in a specific column or set of columns to the locations of the corresponding rows in the table. This allows the database management system to quickly locate the rows that match a specific query, without having to scan the entire table.

When we create an index in SQL, the database management system sorts the values in the indexed column or columns, and then creates a data structure that maps these values to the corresponding rows. When a query is executed that includes a condition on the indexed column or columns, the database management system can use the index to quickly locate the matching rows.

How to create an index in SQL?

To create an index in SQL, you can use the "CREATE INDEX" statement. The syntax of the statement is as follows:

Syntax:

CREATE [UNIQUE] INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • index_name is the name you want to give to the index.
  • table_name is the name of the table where you want to create the index.
  • column1, column2, ... are the names of the columns that you want to include in the index. You can include multiple columns in an index, but keep in mind that the order of the columns can affect the performance of the index.
  • ASC or DESC specify the sort order of the indexed columns. ASC means ascending order, while DESC means descending order.
CREATE INDEX idx_employee_name
ON employees (last_name, first_name);

This example creates an index named idx_employee_name on the employees table, based on the values in the last_name and first_name columns.

How does index work in SQL?

In SQL, an index works by creating a mapping of values in a specific column or set of columns to the locations of the corresponding rows in a table. This allows the database management system to quickly locate the rows that match a specific query, without having to scan the entire table.

When you create an index in SQL, the database management system will sort the values in the indexed column or columns, and then create a data structure that maps these values to the corresponding rows. This data structure is optimized for fast search and retrieval of data.

When a query is executed that includes a condition on the indexed column or columns, the database management system can use the index to quickly locate the matching rows.

The process works as follows:

  • The database management system looks up the values in the index for the specified column or columns, and finds the location of the matching rows in the table.
  • The database management system retrieves the matching rows from the table based on the locations found in the index.

By using an index, the database management system can avoid having to scan the entire table to find the matching rows, which can significantly improve query performance.

Example:

Here is an example to demonstrate how an index works in SQL. Let's say you have a table named employees with the following data:

idlast_namefirst_namesalary
1SmithJohn50000
2JohnsonMichael55000
3JacksonSarah60000
4BrownDavid65000
5DavisJessica70000

Let's say you want to retrieve all the employees with a salary greater than 60,000. Without an index, the database management system would have to scan the entire employees table and check the value of the salary column for each row to find the matching rows. This would be a slow and inefficient process for large tables.

To improve the performance of this query, you can create an index on the salary column:

CREATE INDEX idx_salary
ON employees (salary);

Now, when you run the query to retrieve all employees with a salary greater than 60,000, the database management system can use the idx_salary index to quickly locate the matching rows:

SELECT *
FROM employees
WHERE salary > 60000;

The database management system would perform the following steps:

  1. Look up the values in the idx_salary index for the salary column.
  2. Find the location of the matching rows in the employees table.
  3. Retrieve the matching rows from the employees table based on the locations found in the index.

The result of the query would be:

idlast_namefirst_namesalary
4BrownDavid65000
5DavisJessica70000

As you can see, by using the idx_salary index, the database management system was able to quickly locate the matching rows, without having to scan the entire employees table. This significantly improves the performance of the query.