HTML
JAVASCRIPT
CSS
PYTHON
JAVA
SQL

SQL Indexes

Understanding SQL Indexes

An SQL index is a data structure that enhances the speed of data retrieval operations on a table. It functions like an index in a book, allowing the database engine to locate rows efficiently without scanning every record. Indexes significantly improve the performance of queries, especially those involving SELECT,WHERE,ORDER BY, JOIN operations. However, they also introduce some overhead during INSERT,UPDATE,and DELETE operations as the index must be updated accordingly.

1. Primary Index

A primary index is automatically created when a PRIMARY KEY constraint is defined. It ensures uniqueness and speeds up lookups

Syntax:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

In this case, id is the primary index.

2. Unique Index

A unique index prevents duplicate values in a column while allowing faster searches.

Syntax:

CREATE UNIQUE INDEX idx_student_email ON students(email);

Example:

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100) UNIQUE
);

Here, the email column will have a unique index automatically created.

3. Composite Index (Multi-column Index)

A composite index involves multiple columns and is used to optimize queries that filter by multiple fields.

Syntax:

CREATE INDEX idx_student_name_age ON students(name, age);

Example:

SELECT * FROM students WHERE name = 'John' AND age = 20;

The composite index accelerates queries that filter by name and age.

4. Clustered Index

A clustered index determines the physical order of data in a table. Each table can have only one clustered index

Syntax:

CREATE CLUSTERED INDEX idx_students_id ON students(id);

Example:

If a PRIMARY KEY is defined, a clustered index is created automatically.

5. Non-Clustered Index

A non-clustered index stores a pointer to the actual row, allowing multiple indexes on a single table.

Syntax:

CREATE NONCLUSTERED INDEX idx_student_age ON students(age);

Example:

  SELECT * FROM students WHERE age > 18;

This index speeds up queries filtering by age.

6. Full-Text Index

A full-text index is used for searching text-based data efficiently.

Syntax:

CREATE FULLTEXT INDEX idx_student_bio ON students(bio);

Example:

SELECT * FROM students WHERE MATCH(bio) AGAINST('computer science');

This enables fast text searches in the bio column.

When to Use Indexes?

  • When querying large datasets frequently.
  • When searching based on multiple columns.
  • When performing JOIN operations across tables.
  • When filtering using WHERE, ORDER BY, or GROUP BY.

When Not to Use Indexes?

  • On small tables where performance gains are negligible.
  • On frequently updated tables, as indexes slow down INSERT, UPDATE, and DELETE operations.
  • When columns contain mostly unique values (unless needed for constraints).

Removing an Index

If an index is no longer needed, it can be deleted.

Syntax:

DROP INDEX idx_student_age ON students;

This removes the idx_student_age index from the students table.

Conclusion

SQL indexes play a vital role in optimizing database performance. Choosing the right type of index based on query patterns can drastically enhance query execution speed. However, over-indexing can degrade performance due to the maintenance cost on write operations. Carefully analyzing database usage patterns is key to effective indexing.

Previous Next