MySQL Interview Questions

Model answers to common MySQL interview questions, grouped by level.

Concept

Model answers to common MySQL interview questions, grouped by level. Practice answering each aloud in under two minutes, and mention MySQL version differences where relevant.

Why It Matters

MySQL is one of the most requested database skills for backend, full-stack, QA, data, DevOps, and embedded tooling roles.

Syntax

Answer structure: definition → how MySQL implements it → example → trade-off or pitfall.

Example

What is MySQL? How is it different from SQL? MySQL is an open-source RDBMS owned by Oracle; SQL is the standard language used to query relational databases. MySQL implements SQL with its own extensions (LIMIT, AUTO_INCREMENT, ON DUPLICATE KEY UPDATE).

What is the default storage engine and port? InnoDB; TCP port 3306 (X Protocol: 33060).

CHAR vs VARCHAR? CHAR(n) is fixed length (padded, trailing spaces removed on read); VARCHAR(n) stores actual length plus 1–2 length bytes. Use CHAR for fixed codes (country, VIN), VARCHAR for variable text.

DELETE vs TRUNCATE vs DROP? DELETE removes selected rows, is transactional, and fires triggers; TRUNCATE removes all rows quickly, resets AUTO_INCREMENT, implicitly commits, and fails if referenced by FKs; DROP removes the table itself.

WHERE vs HAVING? WHERE filters rows before grouping; HAVING filters groups after aggregation (MySQL also allows SELECT aliases in HAVING).

Primary key vs unique key? One PK per table, NOT NULL, and in InnoDB it is the clustered index; many unique keys allowed, and they permit multiple NULLs.

What does AUTO_INCREMENT do, and why are there gaps? Generates increasing integers; gaps appear after rollbacks, failed inserts, INSERT IGNORE, and bulk-insert pre-allocation. Values are not reused (counter persisted since 8.0).

How do you paginate in MySQL? ORDER BY ... LIMIT count OFFSET offset; for large tables use keyset pagination (WHERE id > last_id ORDER BY id LIMIT n).

UNION vs UNION ALL? UNION removes duplicates (extra work); UNION ALL keeps all rows and is faster.

How do you create a user and grant read-only access? CREATE USER 'ro'@'10.0.0.%' IDENTIFIED BY '...'; GRANT SELECT ON db.* TO 'ro'@'10.0.0.%';

InnoDB vs MyISAM? InnoDB: transactions, row locks, MVCC, FKs, crash recovery. MyISAM: table locks, no transactions/FKs, needs repair after crashes. Use InnoDB.

What is a clustered index in InnoDB? Why keep the PK small? Rows are stored in the PK B+tree; secondary indexes store the PK value as the row pointer, so a large PK enlarges every secondary index and random PKs cause page splits.

Explain the leftmost-prefix rule. A composite index (a, b, c) can seek on a, a+b, or a+b+c, not on b alone (except via skip scan in limited cases).

What is a covering index? An index containing all columns the query needs; EXPLAIN shows Using index, and no table lookups occur.

REPLACE vs INSERT ... ON DUPLICATE KEY UPDATE vs INSERT IGNORE? REPLACE deletes then inserts (new ID, cascades, triggers); ODKU updates the existing row; IGNORE skips conflicting rows and downgrades some errors to warnings. Prefer ODKU for upserts.

What is ONLY_FULL_GROUP_BY? An SQL mode (default in 5.7+) that rejects selecting non-aggregated columns not functionally dependent on the GROUP BY columns (error 1055).

Does MySQL support FULL OUTER JOIN, INTERSECT, EXCEPT, CHECK constraints? No FULL OUTER JOIN (emulate with UNION). INTERSECT/EXCEPT since 8.0.31. CHECK constraints enforced since 8.0.16.

How do you find slow queries? Slow query log, sys.statement_analysis, Performance Schema digests; then EXPLAIN/EXPLAIN ANALYZE.

What are views' ALGORITHM options? MERGE (query merged into the outer query), TEMPTABLE (materialized into a temporary table), UNDEFINED (MySQL chooses).

utf8 vs utf8mb4? utf8 is an alias for utf8mb3 (max 3 bytes, no emoji/all Unicode); utf8mb4 stores all Unicode and is the MySQL 8 default.

What is the default isolation level and how does InnoDB implement it? REPEATABLE READ, using MVCC consistent snapshots for plain SELECTs and next-key locks (record + gap) for locking reads and writes to prevent phantoms.

Explain gap locks and next-key locks. A gap lock locks the space between index records (prevents inserts); a next-key lock is a record lock plus the gap before it. They appear in range scans at REPEATABLE READ and can cause unexpected blocking; READ COMMITTED largely disables gap locking (except for FK and duplicate checks).

Deadlock vs lock wait timeout? Deadlock (1213): a cycle detected immediately; InnoDB rolls back one whole transaction — retry it. Lock wait timeout (1205): waited longer than innodb_lock_wait_timeout; by default only the statement is rolled back.

Redo log vs undo log vs binary log? Redo: InnoDB write-ahead log for crash recovery (physical). Undo: old row versions for rollback and MVCC. Binlog: server-level logical log of changes for replication and PITR.

What does innodb_flush_log_at_trx_commit control? Redo durability at commit: 1 = flush on every commit (durable), 2 = write at commit and flush each second, 0 = write and flush each second.

How does replication work? Async vs semi-sync vs Group Replication? The source writes binlog events; replicas receive them into relay logs and apply them. Async does not wait; semi-sync waits for at least one replica to acknowledge receipt; Group Replication uses consensus among members for virtually synchronous replication and automatic failover (InnoDB Cluster).

What is GTID? A global transaction identifier (server_uuid:sequence) enabling auto-positioning, easier failover, and skipping specific transactions.

How do you perform point-in-time recovery? Restore the last full backup, then replay binary logs from the backup's position up to just before the bad event using mysqlbinlog --stop-datetime/--stop-position (or exclude a GTID).

How would you design a 1-billion-row time-series table in MySQL? Compact types, PK starting with the entity and time ((device_id, ts)), RANGE partitioning by time, automated partition rotation and retention, summary tables for dashboards, bulk loading with LOAD DATA, replicas for reads.

How do you index a JSON attribute? A generated column on the JSON path with a secondary index, a functional index, or a multi-valued index for arrays.

How do you prevent double booking in MySQL? Unique constraints (e.g., a generated "active" key column for conditional uniqueness), atomic conditional updates (UPDATE ... WHERE booked_by IS NULL), or SELECT ... FOR UPDATE in a transaction.

How do you make a large schema change without downtime? ALGORITHM=INSTANT/INPLACE, LOCK=NONE where supported; otherwise gh-ost or pt-online-schema-change; expand/contract migrations.

41.4 Coding questions (with solutions on the sample database) (BETWEEN is safe here because order_date is a DATE, not a DATETIME.)

-- 1. Second-highest salary
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);          -- 150000.00

-- 2. Nth highest distinct salary (N = 3)
SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) dr FROM employees) t WHERE dr = 3;   -- 120000.00

-- 3. Departments with no employees
SELECT d.dept_name FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id WHERE e.emp_id IS NULL;   -- Finance

-- 4. Duplicate emails in a table (template)
SELECT email, COUNT(*) FROM customers WHERE email IS NOT NULL GROUP BY email HAVING COUNT(*) > 1;              -- none in sample

-- 5. Delete duplicates keeping the lowest id (template, MySQL multi-table DELETE)
-- DELETE t1 FROM contacts t1 JOIN contacts t2 ON t1.email = t2.email AND t1.id > t2.id;

-- 6. Manager with most direct reports
SELECT m.first_name, COUNT(*) AS reports
FROM employees e JOIN employees m ON m.emp_id = e.manager_id
GROUP BY m.emp_id ORDER BY reports DESC LIMIT 1;                                                              -- Asha, 4

-- 7. Running total by date
SELECT order_id, order_date, SUM(total_amount) OVER (ORDER BY order_date) AS running FR
…

Expected Result

You can answer each question with a definition, a MySQL-specific detail, and an example.

Common Mistakes

Answering with generic SQL when the question is MySQL-specific (e.g., isolation defaults, clustered PK).

Forgetting version caveats (CTEs/windows need 8.0).

Best Practices

Mention the MySQL version you assume.

Tie answers to experience (a slow query you fixed, a deadlock you resolved).

Practice Questions

Explain MVCC to a junior developer using a two-session example.

Explain why a query with WHERE DATE(created_at) = CURDATE() is slow and fix it.

Describe how you would migrate a MyISAM table to InnoDB in production.