An index in SQL is a data structure that speeds up data retrieval on a table by giving the database a fast path to locate rows, similar to how a book's index lets you find a topic without reading every page. They trade extra storage and slower writes for much faster reads.
Key Points: • Indexes are commonly built on B-tree structures, which keep lookups, range scans, and sorted access efficient even on large tables. • Hash indexes offer very fast equality lookups but don't support range queries. • A single-column index covers one column; a composite index covers multiple columns together, useful for queries that filter on several columns at once. • A unique index enforces uniqueness in addition to speeding up lookups. • Every index adds overhead to INSERT, UPDATE, and DELETE operations because the index structure must be kept in sync with the data.
Example: Without an index on a large employees table's email column, looking up one employee by email forces a full table scan; adding an index on email lets the database jump almost directly to the matching row.
Code Example:
CREATE INDEX idx_employees_email ON employees(email);
CREATE INDEX idx_employees_dept_name ON employees(department_id, name);Interview Tip: A concise interview answer is:
"An index is a data structure, usually a B-tree, that lets the database find rows quickly instead of scanning the whole table, much like a book's index. I add indexes on columns that are frequently filtered, joined, or sorted on, but I'm mindful that every index adds write overhead, so I don't index columns that are rarely queried."