Conceptual Interview Questions

How to answer#

Interviewers aren't checking if you memorized a definition. They want to hear that you know the behavior and the trade-off. Give the short answer first, then one concrete example.

1. What's the difference between DELETE, TRUNCATE, and DROP?#

DELETE removes rows matching a condition, one by one, fires triggers, can be rolled back, and keeps the AUTO_INCREMENT counter. TRUNCATE empties the table quickly, resets the counter, and implicitly commits. DROP removes the table itself, definition and all. The key point is that the last two aren't transactional in MySQL.

2. WHERE versus HAVING?#

WHERE filters rows before grouping and can't use aggregates. HAVING filters groups after aggregation. Non-aggregate conditions belong in WHERE, since filtering earlier means less data to group.

3. What are the join types?#

INNER keeps matching rows. LEFT keeps all left rows with NULLs where the right doesn't match. RIGHT is the mirror. CROSS is every combination. A self join joins a table to itself. A good follow-up point: a WHERE condition on the right table of a LEFT JOIN turns it into an inner join.

4. How does an index work, and when does it hurt?#

InnoDB indexes are B-trees that keep keys sorted, so lookups and range scans avoid reading the table. They cost disk space and slow writes, because each insert or update maintains them. They don't help when the query wraps the column in a function, uses a leading wildcard LIKE, or matches most of the table.

5. What's a clustered index?#

In InnoDB, the table data is stored in the primary key's B-tree, so the primary key is the clustered index. Secondary indexes store the primary key value as their pointer. That's why short, monotonically increasing primary keys perform better than long random ones.

6. Explain the leftmost prefix rule.#

A composite index on (a, b, c) can serve queries filtering on a, a,b, or a,b,c, but not b or c alone. It's sorted by a, then b within each a, and so on. Column order should put equality columns first, then range or sort columns.

7. What is a covering index?#

An index containing every column a query needs, so MySQL answers from the index without reading table rows. EXPLAIN shows Using index in Extra.

8. What does ACID mean?#

Atomicity: all or nothing. Consistency: constraints hold. Isolation: concurrent transactions don't interfere improperly. Durability: committed data survives crashes. InnoDB uses undo logs for atomicity and rollback, redo logs for durability, and locks plus MVCC for isolation.

9. What are the isolation levels, and what's the default?#

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. MySQL's default is REPEATABLE READ, where a transaction sees a consistent snapshot. They differ in which anomalies they allow: dirty reads, non-repeatable reads, and phantoms.

10. What is MVCC?#

Multi-version concurrency control. InnoDB keeps older versions of changed rows in the undo log, so readers see a consistent snapshot without blocking writers, and writers don't block readers. Plain SELECTs are non-locking; SELECT ... FOR UPDATE takes locks.

11. What's a deadlock, and how do you handle it?#

Two transactions each hold a lock the other needs. InnoDB detects the cycle and aborts one with error 1213. The application should catch it and retry. To reduce them, access rows in a consistent order and keep transactions short.

12. InnoDB versus MyISAM?#

InnoDB supports transactions, row-level locks, foreign keys, and crash recovery. MyISAM has table locks, no transactions, and no foreign keys. InnoDB is the default and the right choice for nearly everything.

13. CHAR versus VARCHAR?#

CHAR(n) is fixed length and padded, suited to short fixed codes. VARCHAR(n) stores only the characters used plus a length prefix. Use VARCHAR by default.

14. Why is NULL tricky?#

NULL means unknown, so NULL = NULL is unknown, not true. Use IS NULL. Aggregates skip NULLs, COUNT(col) doesn't count them, and NOT IN with a NULL in the list returns nothing.

15. UNION versus UNION ALL?#

UNION removes duplicates, which costs a sort or hash. UNION ALL keeps them and is faster. Use UNION ALL when duplicates are impossible or acceptable.

16. What's normalization, and why denormalize?#

Normalization organizes data so each fact is stored once, preventing update anomalies. Denormalization duplicates data deliberately, to avoid joins in read-heavy workloads, at the cost of keeping copies consistent.

17. How do you find and fix a slow query?#

Find it with the slow log or sys.statement_analysis. Run EXPLAIN ANALYZE. Compare rows examined to rows returned. Fix with the right index, a rewrite, or less data. Re-measure.

18. What's the difference between a view and a table?#

A table stores data. A view stores a query definition that runs when you select from it. Views simplify and restrict access but aren't a cache.

19. Is a replica a backup?#

No. Replication copies mistakes instantly, including a bad DELETE. Backups plus binary logs give you point-in-time recovery.

20. How do you prevent SQL injection?#

Parameterized queries, so values are never parsed as SQL. Least-privilege accounts limit the damage if something slips through. Allow-list any dynamic identifiers.

Practice#

Choose five questions above and answer them out loud in under 60 seconds each, with one concrete example per answer. Record yourself, and note where you hesitated. Those are the topics to reread.