SQL Indexing, Performance, and Database Design Interview Questions

15 questions on indexes, query plans, normalization, pagination, partitioning, and designing schemas.

1. 1. What is an index, and how does it speed up queries?

   An index is a separate data structure, usually a B-tree, that keeps column values in sorted order with pointers to rows. The database can find matching rows without scanning the whole table. The trade-offs are extra storage and slower inserts, updates, and deletes, because every index must be maintained.

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

   A clustered index defines the physical order of the rows, so a table can have only one; in MySQL InnoDB the primary key is the clustered index. A non-clustered (secondary) index is a separate structure whose entries point to the rows, and a table can have many.

3. 3. What is a composite index, and what is the leftmost prefix rule?

   A composite index covers several columns in a defined order. The database can use it for queries that filter on the first column, or the first two, and so on, but generally not for a query that skips the leading column. An index on (last_name, first_name) helps a search on last_name alone, but not on first_name alone.

4. 4. What is a covering index?

   An index that contains every column a query needs, so the database can answer from the index alone without touching the table rows. It avoids extra lookups and can speed up read-heavy queries considerably.

5. 5. Why might the database ignore your index?

   Common reasons: applying a function to the indexed column (WHERE YEAR(created_at) = 2026), a leading wildcard (LIKE '%abc'), implicit type conversion, low selectivity (the index would match most of the table, so a scan is cheaper), outdated statistics, or a composite index used out of order. Rewrite predicates to be sargable, for example created_at >= '2026-01-01' AND created_at < '2027-01-01'.

6. 6. How do you analyze and tune a slow query?

   Run EXPLAIN (or EXPLAIN ANALYZE) to see the plan: whether it uses indexes, scans full tables, the join order, and estimated versus actual row counts. Then add or adjust indexes, rewrite the query (select only needed columns, avoid functions on indexed columns, replace correlated subqueries with joins), update statistics, and measure again. Check the slow query log to find candidates.

7. 7. What is normalization? Explain 1NF, 2NF, and 3NF.

   Normalization organizes tables to reduce redundancy and update anomalies. 1NF: each column holds a single atomic value and there are no repeating groups. 2NF: it is in 1NF and every non-key column depends on the whole primary key (no partial dependency). 3NF: it is in 2NF and non-key columns depend only on the key, not on other non-key columns (no transitive dependency).

8. 8. When would you denormalize?

   When reads dominate and joins become a bottleneck, you can store redundant data (such as a precomputed total or a copied name) to make reads cheaper. The cost is extra storage and the need to keep copies consistent, so do it deliberately, after measuring, and document it.

9. 9. How do you model one-to-many and many-to-many relationships?

   One-to-many: put a foreign key on the 'many' side (orders.customer_id). Many-to-many: create a junction table with foreign keys to both sides, and a composite primary key or unique constraint on the pair.

   CREATE TABLE enrollments (
     student_id INT NOT NULL REFERENCES students(id),
     course_id  INT NOT NULL REFERENCES courses(id),
     PRIMARY KEY (student_id, course_id)
   );

10. 10. Why is OFFSET pagination slow on large tables, and what is the alternative?

    OFFSET n makes the database read and discard n rows before returning the page, so deep pages get slower. Keyset (seek) pagination remembers the last seen key and asks for the next rows after it, which uses an index and stays fast.

    SELECT id, title FROM posts
    WHERE id < 1050      -- last id from the previous page
    ORDER BY id DESC
    LIMIT 20;

11. 11. What is the N+1 query problem?

    Code loads a list with one query and then runs one extra query per row to fetch related data, turning 1 query into N+1. It is common with ORMs and lazy loading. Fix it with a join, an eager fetch (a single query with the related rows), or a batched IN query.

12. 12. What is the difference between partitioning and sharding?

    Partitioning splits one large table into pieces inside a single database server, typically by range, list, or hash, which helps pruning and maintenance. Sharding spreads data across multiple database servers, which scales storage and throughput but complicates joins, transactions, and rebalancing.

13. 13. What is replication, and what is replica lag?

    Replication copies data from a primary to one or more replicas. Replicas can serve reads and act as failover targets. With asynchronous replication a replica can be slightly behind, so a read immediately after a write may return old data; routes needing fresh data should read from the primary.

14. 14. How do you choose between SQL and NoSQL databases?

    Choose a relational database when you need structured data, joins, strong consistency, and transactions. Consider NoSQL (document, key-value, wide-column, graph) for flexible schemas, extreme write throughput or horizontal scale, or access patterns that fit its model. Many systems use both, and modern relational databases also handle JSON well, so the choice is rarely absolute.

15. 15. How do you protect against SQL injection?

    Use parameterized queries or prepared statements so user input is always treated as data, never concatenated into SQL. Give the application account only the privileges it needs, validate input, and avoid dynamic SQL; if you need dynamic identifiers, allow-list them.

    -- unsafe: building SQL by string concatenation
    -- safe: SELECT * FROM users WHERE email = ?   (value bound separately)