Types of Indexing

Lecture 8 · ten index types, each with the definition and the SQL or diagram that goes with it.

1 · Primary index

A primary index is created on the primary key of a table.

CREATE TABLE Student (
    Student_ID INT PRIMARY KEY,
    Name       VARCHAR(50),
    Department VARCHAR(20)
);
-- Here, the DBMS automatically creates an index on Student_ID.

2 · Secondary index

A secondary index is created on a non-primary-key attribute.

CREATE INDEX idx_department ON Student(Department);

-- now searching by department becomes faster:
SELECT * FROM Student WHERE Department = 'CSE';

3 · Unique index

A unique index does not allow duplicate values.

CREATE UNIQUE INDEX idx_email ON Student(Email);
-- ensures that one email cannot be used by more than one student

4 · Composite index

A composite index is created using more than one column.

CREATE INDEX idx_dept_cgpa ON Student(Department, CGPA);

-- useful for queries like:
SELECT * FROM Student
WHERE Department = 'CSE'
ORDER BY CGPA DESC;

Column order matters: this index helps WHERE Department = … and WHERE Department = … ORDER BY CGPA, but not a query that filters on CGPA alone (the leftmost-prefix rule).

5 · Clustered index

6 · Non-clustered index

7 · Ordered index

An ordered index keeps the search-key values in sorted order.

So ordered index means the index entries are sorted.

8 · Dense index

SELECT * FROM Student WHERE Student_ID = 104;
-- the DBMS finds 104 in the dense index and follows the pointer to row R4

Dense indexes are commonly used for secondary indexes, but they can also be used with primary indexes.

9 · Sparse index

10 · Multilevel index

A multilevel index means an index is built on top of another index. It is used when the first-level index itself becomes large.

Next: B-Tree indexes →