MySQL Interview Questions for Data Engineers and Analysts

InnoDB supports transactions, row-level locking, foreign keys, and crash recovery via the redo log, and stores rows in a clustered index; MyISAM has only table-level locking, no transactions, and no foreign-key enforcement. InnoDB has been the default since MySQL 5.5, and in MySQL 8 even the data dictionary is InnoDB. Use InnoDB for everything new — MyISAM survives only in legacy read-only workloads.

WHERE filters rows before grouping and cannot reference aggregates; HAVING filters after GROUP BY and can use aggregates like COUNT(*) or SUM(). Filtering in WHERE is cheaper because rows are eliminated before aggregation. Example: WHERE country = 'IN' GROUP BY city HAVING COUNT(*) > 100.

MySQL supports INNER, LEFT, RIGHT, and CROSS joins but has no FULL OUTER JOIN. Emulate it with a LEFT JOIN UNION a RIGHT JOIN (UNION removes the overlap), or with UNION ALL plus an anti-join (WHERE l.id IS NULL) on the second query to avoid the dedup cost. Interviewers ask this to check you know the limitation, not just join syntax.

DELETE is DML: row-by-row, supports WHERE, is transactional, and fires triggers. TRUNCATE is DDL: it deallocates the table, resets AUTO_INCREMENT, causes an implicit commit so it cannot be rolled back, and skips DELETE triggers. DROP removes the table definition, data, and indexes entirely.

InnoDB uses B+tree indexes for both the clustered primary key and secondary indexes; MySQL also offers FULLTEXT and SPATIAL indexes, and HASH indexes on the MEMORY engine. MySQL 8 added descending, functional, and invisible indexes. The key structural fact: the clustered index holds the actual row data, and every secondary index stores the primary key as its row pointer.

A composite index on (a,b,c) supports the leftmost prefixes — a; a,b; a,b,c — and a range condition on one column stops use of the columns after it, so put equality columns first and the range or sort column last. WHERE b = ? alone cannot seek that index (MySQL 8's skip scan helps only in narrow low-cardinality cases). A covering index contains every column the query needs, so InnoDB answers from the index alone — shown as "Using index" in Extra.

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (the default), and SERIALIZABLE. InnoDB's REPEATABLE READ uses MVCC snapshots for consistent reads and next-key locks — record plus gap locks — on locking reads, which is how it prevents phantoms even though the SQL standard does not require it at that level. High-write teams often drop to READ COMMITTED to reduce gap-lock contention and deadlocks.

Atomicity comes from undo logs, durability from the redo log flushed at commit (innodb_flush_log_at_trx_commit=1) plus the doublewrite buffer that protects against torn pages, and isolation from MVCC plus locking. When the binlog is enabled, InnoDB runs an internal two-phase commit so the binlog and redo log can never disagree after a crash. That binlog–redo 2PC is a favourite senior-round follow-up.

The source writes changes to the binary log (ROW format is the default and safest); replicas pull it with an IO thread and apply it with SQL applier threads, asynchronously by default. Semi-synchronous replication makes commit wait for at least one replica's acknowledgment, and GTIDs give every transaction a global ID so failover uses SOURCE_AUTO_POSITION instead of binlog file/offset math. Lag is checked with SHOW REPLICA STATUS and reduced with parallel appliers (replica_parallel_workers) and smaller transactions.

Use ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) in a derived table and filter rn <= 3 outside it. Know the difference: ROW_NUMBER() is always unique, RANK() leaves gaps after ties, DENSE_RANK() does not. Window functions need MySQL 8.0+ — on the 5.7 systems still common in services projects, the fallback is a correlated subquery, and saying so signals real-world exposure.

Procedures cut network round-trips and centralize logic — Indian BFSI and services codebases run on them, so CREATE PROCEDURE, DELIMITER, IN/OUT parameters, and cursors are fair game. The drawbacks: they are hard to version-control and unit test, they hide business logic from the application, and they scale with the single database server rather than horizontally. In a modern data stack the same transformation belongs in dbt or Spark; arguing both sides is the strong answer.

The usual causes: a function or expression wraps the column (DATE(created_at) = ...), implicit type conversion (a string column compared to a number) or a charset/collation mismatch on a join (utf8mb3 vs utf8mb4), a leading-wildcard LIKE '%term', or low selectivity where the optimizer prices a full scan cheaper than millions of secondary-index lookups plus clustered-index hits. Diagnose with EXPLAIN (key is NULL) and refresh stale statistics with ANALYZE TABLE. FORCE INDEX is a last resort — fixing sargability is the real answer.

InnoDB stores the table inside the primary-key B+tree and every secondary index stores the PK as its pointer — so a long PK bloats every index, and random UUIDv4 values insert into random pages, causing page splits and buffer-pool churn. Use AUTO_INCREMENT, or store UUIDs with UUID_TO_BIN(uuid, 1), which swaps the timestamp bits so inserts become roughly sequential. This question reliably separates people who have run MySQL at scale from people who have only queried it.

MySQL supports RANGE, LIST, HASH, and KEY partitioning — typically RANGE on a date column — and the wins are partition pruning on time-bounded queries and instant retention via ALTER TABLE ... DROP PARTITION instead of huge DELETEs. Hard constraints: every unique key including the primary key must contain the partitioning column, and partitioned InnoDB tables cannot have foreign keys. It does not replace good indexing, and when write volume outgrows one server the answer is sharding, not more partitions.