Interview Q&A / SQL & DBMS

1. What are the disadvantages of having too many indexes?

   Every index uses disk and memory, and must be updated on each INSERT, UPDATE and DELETE, which slows writes. Extra indexes can also confuse the optimizer and slow maintenance such as bulk loads and backups.

   BeginnerQuick answer1 min read
2. What is an index in a database and how does it speed up queries?

   An index is a separate sorted structure, usually a B-tree, that maps column values to row locations. It lets the database jump to matching rows instead of scanning the whole table, at the cost of extra storage and slower writes.

   BeginnerQuick answer1 min read
3. What is the difference between a unique index and a primary key?

   Both enforce uniqueness and create an index. A primary key also forbids NULL, there is only one per table, and it identifies each row. A table can have many unique indexes, and they usually allow NULLs.

   BeginnerQuick answer1 min read
4. What is a composite index and what is the leftmost prefix rule?

   A composite index covers several columns in a fixed order. It can be used for queries filtering on its leading columns, such as (a) or (a, b), but generally not for a filter on b alone.

   IntermediateQuick answer1 min read
5. What is a covering index?

   A covering index contains every column a query needs, so the database answers from the index alone without visiting the table. This is called an index-only scan and avoids extra lookups.

   IntermediateQuick answer1 min read
6. Why might the database ignore an index on a column?

   Common reasons are wrapping the column in a function, a leading wildcard in LIKE, a type mismatch, a condition that matches most rows, or outdated statistics. The optimizer then prefers a full scan.

   IntermediateQuick answer1 min read