INDEX in SQL

An INDEX is a database object that improves the speed of data retrieval operations on a table at the cost of additional writes and storage. Indexes allow the database to find and access rows faster, similar to an index in a book.

Why Use Indexes?

• Speed up SELECT queries.

• Improve performance of WHERE, JOIN, ORDER BY, and GROUP BY clauses.

• However, they add overhead on INSERT, UPDATE, and DELETE because the index needs to be maintained.

Types of Indexes:

• Primary Key Index: Automatically created when you define a primary key.

• Unique Index: Ensures all values in the indexed column(s) are unique.

• Non-Unique Index: Speeds up queries but allows duplicate values.

• Composite Index: Index on multiple columns.

• Full-text Index: For text search.

How to Create an Index

1. Creating a simple index

CREATE INDEX index_nameON table_name (column_name);

2. Creating a composite index (multiple columns)

CREATE INDEX index_nameON table_name (column1, column2);

Example:

CREATE INDEX idx_lastnameON employees (last_name);

This index helps speed up queries filtering or sorting by last_name.

How to Drop an Index

DROP INDEX index_name ON table_name;

Notes:

• Indexes speed up reads but slow down writes.

• Too many indexes can degrade performance.

• Use indexes wisely on columns frequently used in search conditions.

If you want, I can help you create optimized indexes for your tables!