MySQL Interview Questions
MySQL is one of the most common database topics in backend and full-stack interviews. These are the questions interviewers actually ask, with concise answers you can speak confidently.
16 questions with concise, interview-ready answers.
1. What is MySQL?
MySQL is an open-source relational database management system (RDBMS) that stores data in tables and uses SQL to query and manage it. It is widely used for web applications, supports the client-server model, and runs on most major operating systems. It is owned by Oracle and powers stacks like LAMP and LEMP.
2. What are storage engines in MySQL, and how do InnoDB and MyISAM differ?
A storage engine is the component that handles how MySQL stores, reads, and writes table data. InnoDB is the default engine: it supports ACID transactions, row-level locking, and foreign keys, making it the right choice for most workloads. MyISAM is older, uses table-level locking, has no transaction or foreign-key support, but historically offered fast reads and full-text search; today InnoDB is almost always preferred.
3. What is the difference between CHAR and VARCHAR?
CHAR is a fixed-length string type that always reserves the declared number of characters, padding shorter values with spaces. VARCHAR is variable-length and stores only the characters used plus a length prefix, so it saves space for values of differing length. Use CHAR for short, uniform values (like country codes) and VARCHAR for most other text.
4. What is the difference between a primary key and a unique key?
A primary key uniquely identifies each row, cannot contain NULL values, and there can be only one per table. A unique key also enforces uniqueness but allows one or more NULL values (depending on the column), and a table can have many unique keys. In InnoDB the primary key also defines the clustered index.
5. What is an index, and what types does MySQL support?
An index is a data structure that speeds up row lookups by avoiding full table scans, at the cost of extra storage and slower writes. MySQL supports several types: primary, unique, regular (non-unique), composite (multi-column), full-text (for text search), and spatial indexes. Most InnoDB indexes are implemented as B-trees.
6. What is a clustered index in InnoDB?
In InnoDB the clustered index physically stores the table rows in the order of the primary key, so the primary key and the data live together in one B-tree. Because of this, there is exactly one clustered index per table. Secondary (non-clustered) indexes store the primary key value as a pointer, which is why an efficient primary key matters.
7. What is a transaction, and what are the ACID properties?
A transaction is a group of SQL statements executed as a single unit that either fully completes or fully rolls back. ACID stands for Atomicity (all-or-nothing), Consistency (the database stays valid), Isolation (concurrent transactions do not interfere), and Durability (committed changes survive crashes). In MySQL, ACID transactions require a transactional engine like InnoDB and are controlled with START TRANSACTION, COMMIT, and ROLLBACK.
8. What are the transaction isolation levels in MySQL?
MySQL supports four isolation levels: READ UNCOMMITTED (allows dirty reads), READ COMMITTED (prevents dirty reads), REPEATABLE READ (prevents non-repeatable reads), and SERIALIZABLE (the strictest, preventing phantom reads). InnoDB's default is REPEATABLE READ, which uses multi-version concurrency control (MVCC) to give consistent snapshots without blocking most reads.
9. What are the different types of JOINs in MySQL?
INNER JOIN returns only rows matching in both tables. LEFT JOIN returns all rows from the left table plus matching right-table rows (NULLs where none match), and RIGHT JOIN does the opposite. MySQL has no native FULL OUTER JOIN, but you can emulate it with a LEFT JOIN UNION a RIGHT JOIN. A CROSS JOIN produces the Cartesian product of both tables.
10. What is the difference between TRUNCATE and DELETE?
DELETE is a DML statement that removes rows one at a time, can use a WHERE clause, fires triggers, and can be rolled back within a transaction. TRUNCATE is a DDL statement that drops and recreates the table to remove all rows quickly, cannot use WHERE, resets AUTO_INCREMENT, and is effectively non-transactional. Use DELETE for selective removal and TRUNCATE to empty a whole table fast.
11. What is AUTO_INCREMENT in MySQL?
AUTO_INCREMENT is a column attribute that automatically generates a unique, increasing integer for each new row, commonly used for primary keys. You insert NULL or omit the column and MySQL assigns the next value. You can retrieve the last generated value with LAST_INSERT_ID(), and the counter does not reuse gaps left by deleted rows.
12. How do you optimize a slow query, and what does EXPLAIN do?
Start by running EXPLAIN (or EXPLAIN ANALYZE) on the query to see the execution plan — which indexes are used, the join order, and how many rows are scanned. Look for full table scans (type ALL) or missing keys, then add appropriate indexes, rewrite the query to be sargable, select only needed columns, and avoid functions on indexed columns in WHERE. The slow query log helps find which queries to optimize in the first place.
13. What is replication in MySQL?
Replication copies data from a source (master) server to one or more replica (slave) servers, so the replicas stay in sync with the source. It is used for read scaling, high availability, and backups. MySQL supports asynchronous, semi-synchronous, and group replication, using either binary-log position-based or GTID-based coordination.
14. How do you back up a MySQL database?
A common logical backup tool is mysqldump, which exports the schema and data as SQL statements, for example mysqldump -u user -p mydb > backup.sql; you restore with mysql -u user -p mydb < backup.sql. For large databases, physical or hot backups (like Percona XtraBackup) are faster and avoid locking. Always test that backups can actually be restored.
15. What is a view in MySQL?
A view is a virtual table defined by a stored SELECT query; it does not store data itself but presents the result of that query when accessed. Views simplify complex queries, provide a consistent interface, and can restrict access to specific columns or rows. Some views are updatable, but those involving joins, aggregates, or DISTINCT generally are not.
16. What is a foreign key constraint?
A foreign key is a column (or set of columns) in one table that references the primary key of another table, enforcing referential integrity between them. It prevents inserting a child row that points to a non-existent parent and can cascade updates or deletes with ON DELETE CASCADE or ON UPDATE CASCADE. Foreign keys require a transactional engine like InnoDB; MyISAM ignores them.
Get these answered live in your real interview
NostrobeAI is a real-time AI interview copilot — it hears the question and drafts a strong answer on your screen, invisible on Zoom, Meet, and Teams. One-time pricing, no subscription.
Try NostrobeAI free