An index is a data structure that improves the speed of searching and retrieving data. It works like the index at the back of a book: instead of scanning every page to find a word, you jump directly to the right page using the index.
Reasons for Creating Indexes
1. Faster Query Performance
Without an index, the database scans every row (called a full table scan). With an index, the database quickly locates the matching rows using the index.
SELECT * FROM Students WHERE roll_no = 101; (If roll_no is indexed, the database finds it instantly.)
2. Efficient Searching & Sorting
Indexes help in ORDER BY, GROUP BY, and DISTINCT queries.
Example: Sorting students by name is faster if the name is indexed.
3. Improves Joins
When combining multiple tables with JOIN, indexes make matching rows faster.
4. Reduces Disk I/O
By avoiding monitoring the entire table, fewer rows are read from disk, which improves performance.
5. Supports Uniqueness
Unique indexes ensure no duplicate values (e.g., emails or usernames).