Complete MySQL interview Q&A covering SQL basics, data types, indexing, JOINs, transactions, ACID, views, triggers, MVCC, and SQL injection - with code examples.
MySQL is one of the most widely used relational databases and a staple of backend and full-stack interviews. This guide covers all MySQL interview questions you're likely to face - from basic SQL syntax to advanced concurrency control - each with a direct answer and code examples.
MySQL is an open-source relational database management system that uses SQL to store and manage data in structured tables. It's developed by Oracle Corporation and has powered web applications for decades - from small side projects to large-scale production systems. If you're building backends with Node.js, PHP, or any server-side language, there's a good chance MySQL is part of the stack.
It comes up constantly in backend and full-stack interviews because it spans a wide range of concepts interviewers care about - data types, constraints, JOINs, transactions, indexing, and security. Knowing MySQL means you understand how relational data actually works, not just how to write a query.
This guide covers every MySQL interview question you're likely to face, organized by topic. Each question has a direct answer and code where it helps.
Key Takeaways
MySQL is a relational database that stores data in structured tables and uses SQL for all data operations
Transactions and ACID properties are a core mid-level topic - know
START TRANSACTION,COMMIT, andROLLBACKcoldInnoDB is the default storage engine and the only one that fully supports transactions and foreign keys in MySQL
Indexing speeds up reads via B-tree lookup but adds overhead on writes - know when to use it and when not to
These questions come up at the start of almost every MySQL interview. They test whether you have a clear mental model of what MySQL is and how it organizes data before the interviewer digs into deeper topics.
SQL stands for Structured Query Language. It's the standard programming language used to manage, manipulate, and communicate with relational databases.
Every operation you perform on a relational database - creating tables, inserting data, filtering rows, joining tables, and deleting records - is expressed as an SQL statement.
MySQL is an open-source RDBMS (Relational Database Management System) that uses SQL to manage and manipulate data. It was developed by Oracle Corporation.
It organizes data into tables made up of rows and columns, enforces relationships between tables through keys, and lets you query and manipulate that data using standard SQL. It's one of the most widely deployed databases in web development.
A database is a storage container that holds data which you can access, modify, and analyze.
In MySQL, a database is a named collection of tables. You can create multiple databases on a single MySQL server - for example, one for your application and another for analytics.
CREATE DATABASE my_app;
USE my_app;DBMS stands for Database Management System - it's the software that lets you interact with and manage databases. MySQL, PostgreSQL, MongoDB, and Microsoft SQL Server are all examples of popular DBMS platforms.
A DBMS handles storage, retrieval, security, backup, and concurrent access to data. MySQL specifically is a Relational DBMS (RDBMS), which means it organizes data in tables with defined relationships.
MySQL supports a variety of data types organized into categories:
INT, DECIMAL, FLOAT, DOUBLECHAR, VARCHAR, TEXT, BLOBDATE, DATETIME, TIMESTAMP, TIMECREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2),
created_at DATETIME,
metadata JSON
);INT stores whole numbers with no decimal points. DECIMAL stores exact decimal values and is the right choice whenever precision matters - like financial figures.
-- INT: whole numbers only
salary INT -- stores 95000, not 95000.50
-- DECIMAL(10, 2): up to 10 digits total, 2 after the decimal point
price DECIMAL(10, 2) -- stores 1299.99 exactlyUse FLOAT or DOUBLE when you need a wide range of values and can tolerate small rounding errors. Use DECIMAL for money.
Both store string data, but they handle storage differently:
CHAR(n) always stores exactly n characters, padding shorter values with spaces.VARCHAR(n) stores only the actual characters entered, up to a maximum of n. It uses less storage for variable-length data but has a small overhead for tracking the length.-- CHAR(10): "Bob" is stored as "Bob " (7 trailing spaces)
code CHAR(10)
-- VARCHAR(255): "Bob" is stored as just "Bob"
name VARCHAR(255)Use CHAR for fixed-length values like country codes or status flags. Use VARCHAR for names, emails, and other variable-length strings.
DATE stores only the calendar date in YYYY-MM-DD format.
DATETIME stores both date and time: YYYY-MM-DD HH:MM:SS.
birthday DATE -- "1995-04-12"
created_at DATETIME -- "2026-06-24 14:30:00"Use DATE when you only care about the calendar date. Use DATETIME when you need to track the exact moment something happened. TIMESTAMP is similar to DATETIME but stores values as UTC and converts to the server's local timezone on retrieval.
These questions cover how MySQL enforces structure and rules within your tables. Interviewers use them to check whether you understand data integrity - not just how to write queries, but how to design tables that don't allow bad data in.
A foreign key is a column (or set of columns) in one table that references the primary key or unique key of another table. Its main purpose is to establish and enforce a link between data in two tables.
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
);This prevents inserting an order with a user_id that doesn't exist in the users table, and prevents deleting a user who still has orders. That enforcement of referential integrity is what foreign keys are for.
AUTO_INCREMENT generates unique, sequential numbers for a column automatically - typically used for primary keys. You don't need to pass the value during inserts; MySQL tracks the current maximum and increments from there.
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO users (name) VALUES ('Alice');
-- id = 1 assigned automatically
INSERT INTO users (name) VALUES ('Bob');
-- id = 2 assigned automaticallyAUTO_INCREMENT only works on integer columns and is typically paired with PRIMARY KEY. It resets when you run TRUNCATE TABLE.
A default constraint assigns a default value to a column when no explicit value is provided during an INSERT. This ensures the column is always valid even if the caller omits it.
CREATE TABLE posts (
id INT AUTO_INCREMENT PRIMARY KEY,
status VARCHAR(20) DEFAULT 'draft',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- status = 'draft' and created_at = now() are set automatically
INSERT INTO posts (id) VALUES (1);A view is a virtual table. It doesn't store any data of its own - it saves a predefined SQL query and runs it dynamically every time you call it.
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';
-- Use it like a regular table
SELECT * FROM active_users WHERE name LIKE 'A%';Views have a few practical advantages:
A trigger is a set of actions that run automatically when a database event occurs. Triggers can be configured to execute BEFORE or AFTER events like INSERT, UPDATE, or DELETE.
CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
INSERT INTO audit_log (user_id, action, deleted_at)
VALUES (OLD.id, 'DELETE', NOW());
END;In this example, every time a row is deleted from users, the trigger automatically logs the deletion to an audit_log table. Triggers run server-side - no application code needed, and they fire regardless of which client sends the query.
SQL aggregate functions perform a calculation on a set of rows and return a single summarized value.
The most common ones:
COUNT() - returns the total number of rows or non-null valuesSUM() - calculates the combined total of all numeric values in a columnAVG() - computes the arithmetic mean of a numeric columnMIN() - finds the smallest value in a columnMAX() - finds the largest value in a columnSELECT
COUNT(*) AS total_orders,
SUM(amount) AS revenue,
AVG(amount) AS avg_order,
MIN(amount) AS smallest,
MAX(amount) AS largest
FROM orders
WHERE status = 'completed';Aggregate functions are almost always paired with GROUP BY to compute summaries per group - for example, total orders per customer.
These come up once the interviewer confirms you understand table structure. JOINs, deletion commands, and transaction handling are the most tested topics at this level.
All four join types combine rows from two tables. The difference is which rows get included when there's no match:
NULLNULLNULL wherever a match is missing-- INNER JOIN: only matched rows
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- LEFT JOIN: all users, even those with no orders
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;MySQL doesn't natively support FULL OUTER JOIN, but you can simulate it by combining a LEFT JOIN and RIGHT JOIN with UNION.
All three remove something from the database, but at different levels:
DELETE. Cannot be rolled back. Resets AUTO_INCREMENT.-- DELETE: remove specific rows
DELETE FROM employees WHERE department_id = 5;
-- TRUNCATE: wipe all rows, keep the table
TRUNCATE TABLE employees;
-- DROP: remove the table entirely
DROP TABLE employees;The key distinction: DELETE is transactional and selective, TRUNCATE is fast but irreversible, DROP destroys the table itself.
A subquery (also called a nested query) is a query embedded inside another query. The inner query runs first, and its result is used by the outer query.
-- Find employees earning above average salary
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);Subqueries can appear in WHERE, FROM, and SELECT clauses. They're useful when you need to filter or compare against a dynamically computed value. For performance-sensitive cases, a JOIN often outperforms a correlated subquery.
ACID is a set of four properties that make database transactions reliable and safe.
If you've worked with COMMIT and ROLLBACK, you're already using ACID in practice. Without these properties, your data can become inconsistent and unreliable in concurrent workloads.
A transaction in MySQL is a sequence of one or more SQL operations executed as a single unit of work. Either all operations succeed, or they all fail together.
You control transactions with three commands:
START TRANSACTION - begins the transactionCOMMIT - saves all changes permanentlyROLLBACK - undoes all changes if something goes wrongSTART TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- If both updates succeed
COMMIT;
-- If anything fails, call this instead
ROLLBACK;Transactions follow ACID properties. They're essential any time you need multiple operations to succeed or fail as a unit - like a bank transfer where money must leave one account and enter another atomically.
Indexing is a way to speed up data retrieval by creating a separate lookup structure that maps column values to their row locations - similar to how a book index works.
Without an index, MySQL performs a full table scan (linear search, O(n)) to find matching rows. With an index, it uses a B-tree structure to perform a binary search (O(log n)), then jumps directly to the matching rows.
-- Create an index on the email column
CREATE INDEX idx_email ON users(email);
-- This query now hits the index instead of scanning the whole table
SELECT * FROM users WHERE email = 'alice@example.com';When to use it: Read-heavy applications where you filter or sort by specific columns frequently.
When not to use it: Write-heavy applications. Every INSERT, UPDATE, or DELETE must also update the index structure and keep it sorted, adding write overhead.
Without an index, the database checks every row to find a match - that's O(n) linear search. An index implements binary search on the indexed column using a B-tree data structure, cutting search time to O(log n).
The database builds a separate lookup table that maps column values to the physical memory locations of their rows. When a query filters by that column, the engine jumps directly to the matching rows instead of scanning the entire table.
The tradeoff is straightforward: faster reads, slower writes. Every write to the table must also update the index structure and keep it sorted.
These come up when the interviewer wants to assess your understanding of MySQL internals, security, and how MySQL fits into a broader backend stack.
MySQL supports multiple storage engines. You choose the engine per table at creation time, and each is optimized for different use cases.
| Engine | Transactions | Foreign Keys | Best For |
|---|---|---|---|
| InnoDB | Yes | Yes | Default - e-commerce, financial systems, anything needing data integrity |
| MyISAM | No | No | Read-heavy workloads like full-text search (largely legacy) |
| Memory | No | No | Temporary tables, session caches - data is lost on server restart |
| CSV | No | No | Data interchange, log files |
| Archive | No | No | Storing large volumes of historical data with minimal disk usage |
| NDB | Yes | Yes | Distributed, high-availability setups (MySQL Cluster) |
-- Specify engine at table creation
CREATE TABLE transactions (
id INT PRIMARY KEY,
amount DECIMAL(10, 2)
) ENGINE=InnoDB;InnoDB is almost always the right choice. It's been the default engine since MySQL 5.5 and is the only engine that fully supports ACID transactions and foreign key constraints.
MVCC stands for Multi-Version Concurrency Control. It's the method MySQL's InnoDB engine uses to handle simultaneous transactions without blocking readers when a write is happening.
Instead of locking a row when a write occurs (which would force readers to wait), MVCC creates a new, timestamped version of that row. This lets a reader safely see an older, consistent snapshot of the data while a writer updates it in parallel.
A concrete example:
1. Transaction A (writer) updates a balance from ₹100 to ₹150 but hasn't committed yet 2. MVCC creates a new version of the row containing ₹150 3. Transaction B (reader) queries the balance at the same time 4. Instead of blocking, the database points Transaction B to the older committed version - it reads ₹100
Once Transaction A commits, the new version becomes visible to future reads. A background process cleans up the expired old versions.
Why it matters:
SQL Injection is a security vulnerability where an attacker manipulates a backend database by inserting malicious SQL code into user input fields.
For example, if a login form expects a username but an attacker inputs ' OR 1=1 --, it changes the logic of the query. The database evaluates 1=1 as true, bypasses authentication, and grants access without a valid password.
-- Vulnerable query - never do this
"SELECT * FROM users WHERE username = '" + userInput + "'"
-- Attacker sends: ' OR 1=1 --
-- Resulting query:
SELECT * FROM users WHERE username = '' OR 1=1 --'
-- Returns all users → authentication bypassedPrevention: Never concatenate raw user input into queries. Use parameterized queries or an ORM, which forces the database to treat all inputs strictly as data values, never as executable SQL.
// Safe: parameterized query (Node.js example)
db.query('SELECT * FROM users WHERE username = ?', [userInput]);ORM stands for Object-Relational Mapping. It's a tool that acts as a translator between your database and your application code.
Instead of writing raw SQL query strings inside your backend code, an ORM lets you interact with the database using the programming language you're already writing in - like JavaScript or Python.
// Raw SQL
const result = await db.query("SELECT * FROM users WHERE id = 1");
// ORM equivalent (Sequelize)
const user = await User.findById(1);ORMs handle query generation, type mapping, and often schema migrations too. The tradeoff: they add an abstraction layer that can hide what SQL is actually being executed, which makes debugging and performance tuning harder. Popular ORMs include Sequelize and Prisma for Node.js, SQLAlchemy for Python, and Hibernate for Java.
Associations in Sequelize are relationships between data models. They map directly to foreign key relationships in your underlying database.
There are four types:
User has one Profile.User has many Posts.Orders belong to one Customer.Users and Roles.// One-to-Many
User.hasMany(Post, { foreignKey: 'userId' });
Post.belongsTo(User, { foreignKey: 'userId' });
// Many-to-Many
User.belongsToMany(Role, { through: 'UserRoles' });
Role.belongsToMany(User, { through: 'UserRoles' });In SQL, there are two primary pagination strategies:
Offset-Based Pagination uses the LIMIT and OFFSET clauses to skip a specific number of rows. It's straightforward to implement and works well for small datasets or UIs where users jump to specific page numbers. It scales poorly on large tables because the database has to scan and discard all previous rows to reach the offset.
-- Page 3 with 10 items per page
SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 20;Cursor-Based Pagination uses a pointer - typically the last fetched row's ID - inside a WHERE clause. It offers constant-time O(1) performance on large datasets because it jumps directly to the index position. It's the preferred approach for infinite scrolling feeds, though it doesn't support jumping directly to arbitrary page numbers.
-- Cursor-based: fetch the next 10 rows after id 347
SELECT * FROM products
WHERE id > 347
ORDER BY id
LIMIT 10;JOINs, indexing, transactions, and ACID properties come up in nearly every interview. Beyond basics, expect questions on storage engines (InnoDB vs MyISAM), SQL Injection prevention, and the difference between DELETE, TRUNCATE, and DROP. For full-stack roles, ORM questions around Sequelize or Prisma are increasingly common.
General SQL knowledge covers most interview topics. MySQL-specific questions will ask about AUTO_INCREMENT, storage engines, and DATETIME vs TIMESTAMP behavior. If the job listing mentions MySQL directly, prepare for those MySQL-specific distinctions alongside standard SQL concepts.
A trigger runs automatically in response to a database event like INSERT, UPDATE, or DELETE on a specific table. A stored procedure is a saved SQL program that you call explicitly with CALL. Triggers are event-driven; stored procedures are invoked on demand.
Use a view when you need to simplify a complex query that multiple parts of your application run repeatedly, or when you want to restrict what data a user can see without duplicating the underlying data. Don't treat views as a performance optimization - they don't cache results and re-execute the underlying query every time they're called.
MySQL's InnoDB engine uses MVCC (Multi-Version Concurrency Control) to handle concurrency. Readers see a consistent snapshot of data from the start of their transaction without blocking writers, and writers don't block readers. For write-write conflicts, InnoDB uses row-level locking to ensure only one transaction modifies a given row at a time.
MySQL interviews test a broad range - from how you define a table to how you keep data safe under concurrent load. The topics that come up most often are JOINs, indexing, transactions, ACID, and SQL Injection prevention. Master those and you'll handle the majority of what interviewers throw at you.
The next step is to practice writing actual queries. Reading answers builds familiarity; writing queries builds the muscle memory that shows in a real interview.