Why your query is slow

Without an index, finding one student by roll number means reading every row. That is a full table scan, and on a million rows it is a million reads to return one.

An index is a separate structure: almost always a B-tree: that maps values to row locations. Looking up a value becomes a handful of reads instead of a million.

CREATE INDEX idx_students_dept ON students(dept_id);

Indexes are not free:

• Every INSERT, UPDATE and DELETE must update them too.
• They take disk.
• An index nothing queries is pure cost.

Clustered vs non-clustered. A clustered index decides the physical order of the rows, so there can be only one: it *is* the table, sorted. A non-clustered index is a separate structure pointing at rows; you can have many.

When an index does not help:

• WHERE UPPER(name) = 'PRIYA': the function defeats it. Index the expression, or store it normalised.
• WHERE name LIKE '%priya%': a leading wildcard has no prefix to seek on.
• A column with two distinct values across a million rows. Reading half the table through an index is slower than scanning it.

Composite indexes are ordered. An index on (dept_id, cgpa) serves WHERE dept_id = 3 and WHERE dept_id = 3 AND cgpa > 8, but not WHERE cgpa > 8 alone: the same reason a phone book sorted by surname cannot find everyone called Priya.

Always check with EXPLAIN. It tells you what the database will actually do, which is frequently not what you assumed.

Takeaway: an index trades write speed and disk for read speed. Add one because a query is measurably slow, never because a column looks important.