SQL Indexes

Definition: An index is a powerful database object that improves the speed of data retrieval operations on a table. It acts as a performance-tuning tool that allows the database engine to find specific rows much faster than it could by searching every single record.

Simple Idea: Think of a database index exactly like the index at the back of a textbook. Instead of flipping through every page of the book to find a specific topic, you look it up in the index to find the exact page number and jump straight there. SQL indexes provide the same "shortcut" for your data.

Example: Creating an Index

If you frequently search for students by their names, you can create an index on the name column to speed up those specific queries.

CREATE INDEX idx_student_name
ON students(name);

How It Works

• Retrieval Speed: When you run a query like WHERE name = 'Ravi', the database checks the idx_student_name first. It finds the location of Ravi's data immediately, rather than performing a "Full Table Scan."
• Storage: An index is a separate structure that takes up additional disk space. It stores a sorted version of the column's data along with pointers to the actual rows in the table.
• Automatic Indexes: Most databases automatically create an index for PRIMARY KEY columns, as these are the most frequently searched fields.

Pros and Cons of Indexing

Key Notes

• The Trade-off: While indexes make reading data (SELECT) much faster, they make writing data (INSERT, UPDATE, DELETE) slightly slower because the database has to update the index every time the data changes.
• When to Index: Only create indexes on columns that are used frequently in WHERE clauses, JOIN conditions, or ORDER BY statements.
• Unique Indexes: You can create a UNIQUE INDEX to both speed up searches and ensure that no two rows have the same value in that column (like an email address).