Autocoder

SQL Indexes Interview Questions

Last updated 2026-11-19

Index questions test whether you can reason about query speed rather than just write queries. The standard set is: what an index is, clustered versus non-clustered, how composite index column order works, what a covering index is, and why a query that "has an index" still does a full scan. Interviewers usually follow with "how would you speed up this query?", where the answer is an index chosen for the actual filter and sort columns.

The answers below are written for MySQL (InnoDB) and PostgreSQL, the two databases most interviews assume, with SQL Server and Oracle noted where terms differ. Execution plans are described in words because the output format of EXPLAIN varies by version. Related pages: DBMS indexing questions, SQL joins and how to crack a DBMS interview.

DatabasesMySQL InnoDB and PostgreSQL, SQL Server and Oracle noted
Core ideaTrade write cost and space for read speed
ToolsEXPLAIN, EXPLAIN ANALYZE

Mental model

A B-tree index stores the indexed columns in sorted order with a pointer to the row. A lookup walks a few levels of the tree (logarithmic in the table size) instead of reading every row. Because the entries are sorted, the same index also serves range filters, ORDER BY and prefix searches. Every INSERT, UPDATE of an indexed column and DELETE must also maintain each index, so indexes speed reads and slow writes.

Questions that follow an index answer

SQL Indexes Interview Questions: questions with worked answers

  1. 1

    What is an index and why does it speed up queries?

    easy

    An index is a separate sorted data structure (usually a B-tree) over one or more columns that lets the database find rows without scanning the whole table. Without an index on email, WHERE email = 'a@x.com' reads every row (a sequential or full table scan, O(n)); with it, the database descends the tree in O(log n) and fetches the matching rows. The price is extra storage and slower writes, since each change updates the index too.

  2. 2

    What is the difference between a clustered and a non-clustered index?

    medium

    A clustered index defines the physical order of the table rows: the table data is stored in the leaf nodes of the index, so there is at most one per table. In MySQL InnoDB the primary key is the clustered index. A non-clustered (secondary) index is a separate structure whose leaves hold the indexed columns plus a pointer: in InnoDB the primary key value, in SQL Server a row id or the clustering key. PostgreSQL tables are heaps with no clustered index by default, and CLUSTER reorders a table once without maintaining the order afterwards. A secondary-index lookup in InnoDB therefore needs a second lookup in the primary key tree unless the index covers the query.

  3. 3

    How does a composite index work, and why does the column order matter?

    medium

    A composite index on (a, b, c) sorts by a, then by b within equal a, then by c. It can be used for filters on a, on a and b, or on a, b and c (the leftmost prefix rule), but not efficiently for b alone. Equality columns should come first, followed by the range column, and then columns used for sorting. For WHERE dept_id = 20 AND salary > 70000 ORDER BY salary, the index (dept_id, salary) serves the filter and the sort. The reversed index (salary, dept_id) can only range-scan on salary and then filter dept_id.

    sql
    CREATE INDEX idx_emp_dept_salary ON employees (dept_id, salary);
    -- uses the index:  WHERE dept_id = 20
    -- uses the index:  WHERE dept_id = 20 AND salary > 70000
    -- cannot seek:     WHERE salary > 70000   (no leading column)
  4. 4

    What is a covering index?

    medium

    An index that contains every column the query needs, so the database answers entirely from the index without visiting the table. EXPLAIN shows "Using index" in MySQL and "Index Only Scan" in PostgreSQL (which also needs a recently vacuumed table, since it checks the visibility map). In InnoDB every secondary index implicitly includes the primary key. PostgreSQL 11+ and SQL Server support INCLUDE columns, which add payload columns to the leaf without affecting the sort order.

    sql
    -- query: SELECT salary FROM employees WHERE dept_id = 20;
    CREATE INDEX idx_cover ON employees (dept_id, salary);          -- covers it
    CREATE INDEX idx_cover2 ON employees (dept_id) INCLUDE (salary); -- PostgreSQL, SQL Server
  5. 5

    Name situations where an index exists but the database does not use it.

    medium

    Applying a function to the indexed column (WHERE LOWER(email) = ..., WHERE YEAR(order_date) = 2026) hides it from the index, so create a functional index or rewrite as a range. A leading wildcard (LIKE '%abc') cannot use a B-tree. Implicit type conversion, such as comparing a varchar column with a number, defeats it in MySQL. The query returns a large fraction of the table, so a sequential scan is cheaper. The table is small. Statistics are stale. A composite index is used without its leading column. Using OR across different columns can prevent a single index being chosen. EXPLAIN shows which case applies.

    sql
    -- bad: function on the column
    SELECT * FROM orders WHERE YEAR(order_date) = 2026;
    -- good: sargable range
    SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';
  6. 6

    What does "sargable" mean?

    medium

    SARGable means "search argument able": the condition can use an index seek. Predicates that compare a bare indexed column with a constant (=, <, >, BETWEEN, IN, LIKE 'abc%') are sargable. Wrapping the column in a function, doing arithmetic on it (WHERE salary + 1000 > 50000) or a leading wildcard makes it non-sargable. The fix is to move the computation to the constant side: WHERE salary > 49000.

  7. 7

    What is the difference between a primary key, a unique index and a normal index?

    medium

    A primary key uniquely identifies rows, cannot be NULL and is backed by a unique index (and is the clustered index in InnoDB). A unique index enforces uniqueness on any column set and allows NULLs in most databases (MySQL and PostgreSQL permit several NULLs; SQL Server allows one). A normal index only speeds up lookups with no constraint. Foreign keys are not automatically indexed in PostgreSQL, so add an index on the referencing column to keep joins and cascades fast. MySQL InnoDB creates one automatically.

  8. 8

    What are the downsides of adding many indexes?

    medium

    Each INSERT, DELETE and update of an indexed column writes to every index, so write throughput drops. Indexes consume disk and memory, compete for the buffer cache, lengthen backups and bloat over time. The optimiser has more plans to consider, and redundant indexes (an index on (a) alongside (a, b)) add cost for no gain. Index what your queries filter, join and sort on, and drop unused ones, which PostgreSQL shows in pg_stat_user_indexes and MySQL in sys.schema_unused_indexes.

  9. 9

    How would you speed up a slow query? Walk through the steps.

    hard

    First reproduce it and read the plan with EXPLAIN (EXPLAIN ANALYZE in PostgreSQL gives actual timings). Look for full scans on large tables, row estimates far from actual, sorts spilling to disk and nested loops over many rows. Then add or reorder an index to match the filter and sort columns, rewrite non-sargable predicates, select only the needed columns so the index can cover, avoid SELECT *, and check that statistics are fresh (ANALYZE). Only after that consider partitioning, caching or schema changes. Say that you measure before and after.

  10. 10

    Explain B-tree versus hash indexes, and other index types.

    medium

    A B-tree supports equality, ranges, ordering and prefix matches, and is the default everywhere. A hash index supports only equality and is rarely better, though MySQL MEMORY tables and PostgreSQL hash indexes (crash-safe since 10) offer it. PostgreSQL also provides GIN (full-text, arrays, JSONB), GiST (geometric, ranges), BRIN (huge naturally ordered tables, tiny index) and partial indexes. MySQL offers FULLTEXT and spatial indexes. Oracle adds bitmap indexes for low-cardinality columns in warehouses.

  11. 11

    What is index cardinality and selectivity, and which columns should you index?

    hard

    Cardinality is the number of distinct values in the column, and selectivity is distinct values divided by rows. A high-selectivity column such as email or order_id narrows the search to a few rows, so an index helps. A low-selectivity column like gender or a boolean gives little benefit alone, because the database would read half the table anyway. Combine it as a trailing column in a composite index, or use a partial index (PostgreSQL) such as WHERE active = true when one value is rare.

    sql
    -- PostgreSQL partial index: only the rare pending rows are indexed
    CREATE INDEX idx_pending ON orders (created_at) WHERE status = 'pending';
  12. 12

    How do indexes interact with ORDER BY, GROUP BY and joins?

    medium

    An index whose order matches ORDER BY lets the database return rows already sorted and stop early with LIMIT, avoiding a sort. GROUP BY can use an ordered index to aggregate without a hash or sort step. For a join, an index on the join column of the inner table enables an index nested loop join, which is why the foreign key column should be indexed. Direction matters in a composite index mixing ASC and DESC: MySQL before 8.0 ignored DESC in index definitions, and 8.0 supports descending indexes.

  13. 13

    What is a full table scan and when is it the right choice?

    hard

    Reading every row. It is the best plan when the query touches a large part of the table, because sequential reads are far cheaper per row than index lookups followed by random table reads, and when the table is small enough to fit in a page or two. The optimiser decides using statistics. A common mistake is forcing index hints for a query that returns 30 percent of the rows and making it slower.

  14. 14

    What happens to an index when you update or delete rows, and what is index bloat?

    medium

    The index entry is updated or marked deleted. In PostgreSQL MVCC leaves dead tuples and dead index entries until VACUUM removes them, and heavy churn leaves the index bloated, which REINDEX or pg_repack fixes. InnoDB page splits and fragmentation have a similar effect, addressed by OPTIMIZE TABLE or rebuilding the index. Updating a primary key in InnoDB is expensive because it moves the row and rewrites every secondary index.

Frequently asked questions

How many indexes should a table have?

There is no fixed number. Index the columns used to filter, join and sort in your real queries and drop unused ones, since each index slows writes.

Does the order of columns in WHERE matter for a composite index?

No. The optimiser reorders conditions. The order of columns in the index definition matters.

Is a primary key automatically indexed?

Yes, in every major database. In InnoDB it is also the clustered index.

Where can I read about how indexes are stored?

See DBMS indexing questions for B+ trees and the file-organisation side.

Related guides

More in Programming Language Interview Questions by Language

See all in this section