SQL Indexing - Learn DA-DS Course in Hindi

SQL Indexing:

Indexing is used to speed up the retrieval of rows from a database table by creating a data structure (index) that allows for faster searching.

a) What is an Index?

An index is a performance optimization technique that allows for faster data retrieval operations. It works like the index in a book, where you can quickly locate a topic without reading every page.

Indexes are typically created on columns that are frequently used in SELECT queries, especially for filtering or sorting.

b) Types of Indexes:

i) Single-Column Index:

An index created on a single column.
Example:

CREATE INDEX idx_employee_name ON Employees (Name);

ii) Composite Index (Multi-Column Index):

An index created on multiple columns.
Example:

CREATE INDEX idx_employee_name_dob ON Employees (Name, DateOfBirth);

iii) Unique Index:

Ensures that all values in the indexed column(s) are unique, like a UNIQUE constraint.
Example:

CREATE UNIQUE INDEX idx_unique_email ON Employees (Email);

iv) Full-Text Index:

Used for full-text searches, typically for string columns where you need to search within text.
Example (MySQL specific):

CREATE FULLTEXT INDEX idx_fulltext_name ON Employees (Name);

c) When to Use Indexes:

Frequent searches: If a column is often used in WHERE clauses or as part of a JOIN, an index can help speed up those queries.
Sorting: Columns used in ORDER BY or GROUP BY clauses may benefit from indexes.
Unique values: Columns with unique values often benefit from unique indexes.

d) Costs of Indexing:

Storage overhead: Indexes consume additional storage space.
Slower write operations: While indexes speed up read operations, they can slow down INSERT, UPDATE, and DELETE operations because the index needs to be updated as well.

e) Dropping an Index:

Example:

DROP INDEX idx_employee_name ON Employees;

Combining Constraints and Indexing:

Primary Keys and Indexing: A PRIMARY KEY automatically creates a unique index on the column(s) involved. Similarly, UNIQUE constraints automatically create a unique index.
Foreign Keys and Indexing: While FOREIGN KEY constraints ensure referential integrity, it is often beneficial to create indexes on foreign key columns to improve performance on JOIN operations.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,           -- Primary Key constraint, automatically creates index
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,            -- Unique constraint, automatically creates index
    DepartmentID INT,
    JoiningDate DATE DEFAULT CURRENT_DATE,
    Age INT CHECK (Age >= 18),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)  -- Foreign Key constraint
);

-- Adding an index on the 'Name' column to improve search performance
CREATE INDEX idx_employee_name ON Employees (Name);