MySQL interview questions

What a strong answer covers

• WHERE filters rows before grouping and aggregation; HAVING filters groups after aggregation.
• WHERE cannot use aggregate functions; HAVING can.
• Example: SELECT department, COUNT(*) FROM employees WHERE salary > 50000 GROUP BY department HAVING COUNT(*) > 5.
• Using HAVING for row-level filtering is inefficient and can produce wrong results if aggregates are involved.
• Mention that HAVING is applied after GROUP BY, so it can reference aggregate expressions.

Where people lose the point

• ×Using HAVING to filter individual rows, which is logically incorrect and slower.
• ×Using WHERE with aggregate functions, which is a syntax error.
• ×Forgetting that HAVING can also filter on non-aggregated columns, but it's less efficient.

What a strong answer covers

• The leftmost prefix rule states that a composite index can be used for queries that filter on the leftmost columns of the index in order.
• Example: index on (last_name, first_name) can be used for queries on last_name alone or last_name + first_name, but not on first_name alone.
• The rule applies to both equality and range conditions, but range conditions on a column prevent using subsequent columns.
• Explain that the optimizer can use the index for sorting and grouping as well.
• Mention that covering indexes can also benefit from the rule.

Where people lose the point

• ×Thinking a composite index can be used for any column in the index.
• ×Ignoring column order when creating composite indexes.
• ×Assuming that a range condition on the first column still allows using the second column for filtering.

What a strong answer covers

• Clustered index (primary key) determines physical row order; secondary indexes store the indexed columns plus the primary key value.
• Lookup via secondary index requires two steps: find the primary key in the secondary index, then look up the row in the clustered index (called a 'bookmark lookup').
• Covering indexes can avoid the second lookup by including all needed columns.
• Clustered index scans are faster for range queries on the primary key.
• Insert performance can degrade if primary keys are not sequential (e.g., UUIDs).

Where people lose the point

• ×Confusing clustered and non-clustered indexes.
• ×Assuming secondary indexes are always slower without considering covering indexes.
• ×Not understanding that the primary key is included in every secondary index, affecting index size.

What a strong answer covers

• READ UNCOMMITTED: allows dirty reads, no prevention.
• READ COMMITTED: prevents dirty reads, but allows non-repeatable reads and phantoms.
• REPEATABLE READ: prevents dirty and non-repeatable reads, and in InnoDB also prevents phantoms using next-key locks (default).
• SERIALIZABLE: prevents all anomalies by locking reads.
• Explain each anomaly: dirty read, non-repeatable read, phantom read.

Where people lose the point

• ×Saying REPEATABLE READ allows phantoms in MySQL (it prevents them in most cases).
• ×Confusing READ COMMITTED with REPEATABLE READ.
• ×Not knowing the default isolation level.

What a strong answer covers

• A deadlock occurs when two or more transactions hold locks and each waits for a lock held by the other.
• InnoDB detects deadlocks and rolls back one transaction (the one with the fewest undo records) and returns an error.
• Prevention strategies: keep transactions short, access tables in a consistent order, use locking reads sparingly, and consider using READ COMMITTED to reduce locking.
• Use SHOW ENGINE INNODB STATUS to analyze deadlock details.
• Retry logic in application code is often necessary.

Where people lose the point

• ×Thinking MySQL resolves deadlocks automatically without any application handling.
• ×Ignoring the order of operations across transactions.
• ×Not knowing how to diagnose deadlocks.

What a strong answer covers

• 1NF: atomic values, no repeating groups.
• 2NF: 1NF plus no partial dependency on a composite key.
• 3NF: 2NF plus no transitive dependency (non-key column depends on another non-key column).
• Example: orders (order_id, customer_id, customer_name) violates 3NF because customer_name depends on customer_id.
• Fix: split into orders and customers tables.

Where people lose the point

• ×Confusing 2NF and 3NF.
• ×Thinking normalization always improves performance.
• ×Not recognizing transitive dependencies.

What a strong answer covers

• EXPLAIN SELECT ... shows the execution plan: table order, join type, possible keys, key used, rows examined, Extra info.
• Key columns: type (ALL, index, range, ref, const), key, rows, Extra (Using index, Using filesort, Using temporary).
• A type of ALL indicates a full table scan; adding an index may help.
• Using filesort indicates sorting that may be optimized with an index.
• Using temporary indicates a temporary table, often for GROUP BY or DISTINCT.

Where people lose the point

• ×Only looking at the 'key' column and ignoring 'type' and 'Extra'.
• ×Not understanding that EXPLAIN estimates rows, not actual.
• ×Forgetting to use EXPLAIN ANALYZE (MySQL 8.0+) for actual execution times.

What a strong answer covers

• A covering index contains all columns needed by a query, so the query can be satisfied entirely from the index without accessing the table.
• This avoids the bookmark lookup for secondary indexes.
• Example: index on (department, salary) can cover SELECT department, salary FROM employees WHERE department = 'Engineering'.
• Covering indexes are especially useful for queries that select only a few columns.
• Trade-off: larger indexes and slower writes.

Where people lose the point

• ×Thinking a covering index must include all columns in the table.
• ×Not realizing that SELECT * cannot be covered by a secondary index.
• ×Ignoring the extra storage cost.

What a strong answer covers

• Atomicity: all or nothing; InnoDB uses undo logs to roll back.
• Consistency: constraints and triggers; transactions move from one valid state to another.
• Isolation: transactions are isolated; InnoDB uses locking and MVCC.
• Durability: committed changes persist; InnoDB uses redo logs and doublewrite buffer.
• Mention that isolation levels control the degree of isolation.

Where people lose the point

• ×Confusing consistency with isolation.
• ×Thinking ACID is only about transactions, not about the database as a whole.
• ×Not knowing the specific mechanisms (redo/undo logs).

What a strong answer covers

• INNER JOIN returns only matching rows from both tables.
• LEFT JOIN returns all rows from the left table and matching rows from the right; unmatched right columns are NULL.
• RIGHT JOIN is the reverse; often avoided by swapping table order.
• Example: SELECT * FROM employees e LEFT JOIN departments d ON e.dept_id = d.id returns all employees even without a department.
• Use LEFT JOIN when you need all rows from the primary table regardless of matches.

Where people lose the point

• ×Using LEFT JOIN when INNER JOIN is intended, leading to NULLs.
• ×Not understanding that RIGHT JOIN can be rewritten as LEFT JOIN.
• ×Forgetting to specify join conditions correctly.

What a strong answer covers

• Query cache stored the result set of SELECT queries and returned them if the same query was issued again.
• It was removed in MySQL 8.0 due to scalability issues and contention on a single mutex.
• It was ineffective for write-heavy workloads because any table change invalidated cached entries.
• Modern alternatives: application-level caching (Redis, Memcached) or MySQL's buffer pool.
• Mention that query cache is not the same as InnoDB buffer pool.

Where people lose the point

• ×Thinking query cache is still available in MySQL 8.0.
• ×Confusing query cache with buffer pool.
• ×Assuming query cache improves performance in all cases.

What a strong answer covers

• SET optimizer_trace='enabled=on'; then run the query; then SELECT * FROM information_schema.OPTIMIZER_TRACE.
• The trace shows the steps of optimization: table join order, index selection, cost estimates.
• It can reveal why an index was not used (e.g., low selectivity).
• Useful for debugging complex queries where EXPLAIN is not enough.
• Remember to disable the trace after use.

Where people lose the point

• ×Not knowing how to enable the trace.
• ×Expecting the trace to be easy to read; it's verbose.
• ×Forgetting to disable the trace, which can impact performance.

What a strong answer covers

• Shared (S) locks allow multiple transactions to read; exclusive (X) locks allow only one transaction to write.
• Record locks lock a single index record.
• Gap locks lock a range between records to prevent inserts.
• Next-key locks combine record and gap locks; used in REPEATABLE READ to prevent phantoms.
• In READ COMMITTED, gap locks are disabled, so phantoms can occur.

Where people lose the point

• ×Thinking gap locks are used in READ COMMITTED.
• ×Confusing shared and exclusive locks with read and write locks.
• ×Not understanding that next-key locks are on index records, not rows.

What a strong answer covers

• Foreign keys enforce referential integrity, preventing orphaned rows.
• They require indexes on the referencing column; MySQL automatically creates one if missing.
• They add overhead to INSERT, UPDATE, DELETE operations because checks are performed.
• In high-write environments, some developers drop FKs and handle integrity in the application for performance.
• Trade-off: integrity vs. performance and flexibility.

Where people lose the point

• ×Thinking foreign keys are always bad for performance.
• ×Not realizing that FK constraints require indexes.
• ×Assuming that dropping FKs is always the right optimization.

What a strong answer covers

• UNION combines rows from multiple SELECT statements with the same column count and types; JOIN combines columns from different tables based on a condition.
• UNION removes duplicates by default; UNION ALL keeps all rows.
• JOIN can be INNER, LEFT, etc., and typically relates tables via keys.
• Example: UNION for combining results from two tables with similar structure; JOIN for combining orders and customers.
• Performance: UNION ALL is faster than UNION because it avoids distinct sorting.

Where people lose the point

• ×Using UNION when you need to combine columns from different tables.
• ×Forgetting that UNION requires the same number of columns.
• ×Not knowing the difference between UNION and UNION ALL.

What a strong answer covers

• JSON type allows storing semi-structured data with validation and efficient access via JSON functions.
• Advantages: flexibility, schema-less, easy to store complex nested data.
• Limitations: harder to index, less efficient for queries that need to filter on JSON fields, no foreign keys.
• Normalized design offers better performance for relational queries and integrity.
• Use JSON for occasional flexible attributes, but avoid overusing it for core data.

Where people lose the point

• ×Thinking JSON is a replacement for normalization.
• ×Not knowing that you can create indexes on generated columns from JSON.
• ×Ignoring the overhead of JSON parsing.

What a strong answer covers

• Replication copies data from a source to replica(s) for read scaling, backup, and disaster recovery.
• Types: asynchronous (default), semi-synchronous, and synchronous (Group Replication).
• Asynchronous: source doesn't wait for replica; risk of data loss on failover.
• Semi-synchronous: source waits for at least one replica to acknowledge.
• Replication is statement-based or row-based; row-based is more robust.

Where people lose the point

• ×Thinking replication is real-time; it's asynchronous by default.
• ×Confusing replication with clustering.
• ×Not understanding the difference between statement and row-based replication.

What a strong answer covers

• Buffer pool caches data and indexes in memory to avoid disk I/O.
• It is the most important tuning parameter; default is 128MB, but often set to 70-80% of available RAM.
• Use SHOW ENGINE INNODB STATUS to see buffer pool hit rate.
• Sizing depends on workload: read-heavy benefits from larger pool.
• Multiple buffer pool instances can reduce contention.

Where people lose the point

• ×Setting buffer pool too small, causing high disk I/O.
• ×Setting it too large, causing swapping.
• ×Not monitoring hit rate.