5 questions developers actually get asked about database indexing — with clear, practical answers you can use to prepare.
A database index is a separate data structure (usually a B-tree) that stores a sorted copy of one or more columns plus pointers back to the full row. When you query an indexed column, the database walks the tree in O(log n) time instead of scanning every row sequentially. It is the database equivalent of the index at the back of a textbook.
Avoid indexes on columns that change frequently (every write also updates the index, slowing inserts), small tables under ~1,000 rows (a full scan is faster than tree traversal at that size), columns with very low cardinality like boolean flags (the index does not narrow results enough), and columns you rarely filter on. Each extra index also costs disk space and memory.
A clustered index physically reorders the table rows on disk according to the index column — there can be only one per table. A non-clustered index is a separate structure that points back to the rows. Clustered is faster for range queries on the indexed column; non-clustered is more flexible because you can have many of them.
A full table scan is when the database reads every single row in a table to find matches, because no usable index exists for the query. It is slow because the time grows linearly with the table size — fine at 100 rows, painful at 100 million. The fix is usually adding an index on the column in the WHERE clause.
Run EXPLAIN (PostgreSQL/MySQL) or EXPLAIN PLAN (Oracle/SQL Server) before your query. The output shows the execution plan — look for "Index Scan" or "Index Seek" (good) vs "Seq Scan" or "Table Scan" (bad). If your query uses Seq Scan despite having an index, the index might not match the query, the column might be wrapped in a function, or the planner decided a scan was cheaper.
Want the full concept, analogies, and AI prompts for database indexing?
Read the full Database Indexing block →