MySQL Interview Questions: Top 40+ Questions with Answers

If you’re preparing for backend, full‑stack, or data‑oriented roles, chances are you’ll face MySQL interview questions in your technical rounds. MySQL is one of the most widely used open‑source relational database management systems, powering everything from small web apps to large enterprise systems.

For a broader preparation beyond databases, also check out our main guide on Technical Interview Questions, which covers core programming, data structures, algorithms, and system design along with MySQL and SQL‑related topics.

Beginner‑level MySQL interview question

1. What is MySQL and where is it used?

MySQL is an open‑source relational database management system (RDBMS) that uses SQL (Structured Query Language) to manage and query data. It’s commonly used in web applications (especially with PHP, Java, Python), content management systems, e‑commerce platforms, and analytics backends because it’s fast, scalable, and easy to integrate with most programming languages.

2. What is the difference between SQL and MySQL?

• SQL is a language used to interact with relational databases (e.g., to create tables, insert data, run queries).
• MySQL is a specific database system that implements SQL and provides storage, security, and performance features.

In short: SQL is the language; MySQL is the database that understands that language.

3. What are the basic SQL commands?

Common basic SQL commands include:

• SELECT – retrieve data from one or more tables
• INSERT – add new rows to a table
• UPDATE – modify existing rows
• DELETE – remove rows from a table
• CREATE – define new database objects (tables, indexes, etc.)
• DROP – remove existing database objects

4. What is a primary key?

A primary key is a column (or a set of columns) that uniquely identifies each row in a table. It must be:

• Unique (no duplicate values).
• Non‑nullable (no NULL values).

Example:

sqlCREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

5. What is a foreign key?

A foreign key is a column (or set of columns) that creates a link between two tables. It refers to the primary key of another table and enforces referential integrity.

Example:

sqlCREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

6. What are the main SQL constraints?

Common constraints include:

• NOT NULL – column cannot contain NULL.
• UNIQUE – all values in the column must be unique.
• PRIMARY KEY – uniquely identifies a row (combines NOT NULL and UNIQUE).
• FOREIGN KEY – links to another table’s primary key.
• CHECK – ensures values meet a condition.
• DEFAULT – sets a default value if no value is provided.

7. What is the difference between CHAR and VARCHAR?

• CHAR(n) – fixed‑length string; always uses n characters of storage (padded with spaces if shorter).
• VARCHAR(n) – variable‑length string; uses only as much space as needed up to n.

Use CHAR for short, fixed values (like country codes); VARCHAR for variable text like names or descriptions.

8. What does NULL mean in MySQL?

NULL represents a missing or unknown value. It is not the same as an empty string or zero. Arithmetic or comparisons involving NULL usually result in NULL unless you use special functions like IS NULL or COALESCE.

9. How do you select all columns from a table?

Use:

sqlSELECT * FROM table_name;

The * means “all columns.” For better performance and clarity, it’s usually better to list specific columns explicitly.

10. How do you filter rows with WHERE?

Use the WHERE clause to filter rows:

sqlSELECT name, age FROM users WHERE age > 25;

You can combine conditions with AND, OR, and NOT.

11. How do ORDER BY and LIMIT work?

• ORDER BY sorts the result set:sqlSELECT * FROM products ORDER BY price DESC;
• LIMIT restricts the number of rows returned:sqlSELECT * FROM products LIMIT 5;

Combine them for pagination‑style queries:

sqlSELECT * FROM products LIMIT 10 OFFSET 20;

12. What is the difference between DELETE and TRUNCATE?

• DELETE removes rows based on a condition; it can be rolled back and fires triggers.
• TRUNCATE removes all rows from a table and usually resets auto‑increment counters; it’s faster and cannot be easily rolled back (in many engines).

Example:

sqlDELETE FROM users WHERE age < 18;
TRUNCATE TABLE logs;

13. What is the difference between DROP and DELETE?

• DROP TABLE removes the entire table (structure and data).
• DELETE removes only data rows; the table structure remains.

DROP is a DDL (Data Definition Language) command; DELETE is a DML (Data Manipulation Language) command.

14. What is an index and why is it important?

An index is a data structure that speeds up data retrieval operations on a table. Just like an index in a book, it helps the database find rows faster without scanning the whole table.

You typically create indexes on columns used in WHERE, JOIN, and ORDER BY clauses.

Example:

sqlCREATE INDEX idx_name ON users(name);

15. What is normalization?

Normalization is a design process to organize data to reduce redundancy and improve data integrity.
Common normal forms include:

• 1NF – each column holds a single value; no repeating groups.
• 2NF – must be in 1NF and fully dependent on the primary key.
• 3NF – must be in 2NF and no transitive dependencies.

Intermediate‑level MySQL interview questions

16. What are the main types of joins?

Common joins on two tables:

• INNER JOIN – returns rows that match in both tables.
• LEFT JOIN – returns all rows from the left table, plus matching rows from the right (or NULL if no match).
• RIGHT JOIN – returns all rows from the right table, plus matching rows from the left.
• CROSS JOIN – returns the Cartesian product (all combinations).

Example:

sqlSELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

17. What is a self‑join?

A self‑join is when a table is joined with itself. You usually use aliases to distinguish the two instances of the same table.

Example (manager–employee hierarchy):

sqlSELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

18. What is a subquery?

A subquery is a SELECT statement nested inside another SQL statement. It can appear in SELECT, FROM, WHERE, or HAVING clauses.

Example:

sqlSELECT name FROM users
WHERE age > (SELECT AVG(age) FROM users);

19. What is the difference between WHERE and HAVING?

• WHERE filters rows before grouping.
• HAVING filters groups after grouping (used with GROUP BY).

Example:

sqlSELECT country, COUNT(*) AS count
FROM users
GROUP BY country
HAVING COUNT(*) > 5;

20. What does GROUP BY do?

GROUP BY groups rows that have the same values in specified columns into summary rows. It’s used with aggregate functions like COUNT, SUM, AVG, MAX, MIN.

Example:

sqlSELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

21. What are aggregate functions?

Common aggregate functions:

• COUNT() – number of rows.
• SUM() – sum of values.
• AVG() – average.
• MAX() – maximum value.
• MIN() – minimum value.

They ignore NULL values (except in some edge cases).

22. What is a view in MySQL?

A view is a virtual table based on the result of a stored query. It doesn’t store data itself but runs the underlying query when accessed.

Example:

sqlCREATE VIEW active_users AS
SELECT name, email FROM users WHERE status = 'active';

You use it later like a table:

sqlSELECT * FROM active_users;

23. What is a stored procedure?

A stored procedure is a named block of SQL code stored in the database that you can call with parameters. It’s useful for reusable, complex logic and can improve performance.

Example (syntax concept):

sqlDELIMITER //

CREATE PROCEDURE GetUsersByAge(IN min_age INT)
BEGIN
    SELECT name, age FROM users WHERE age >= min_age;
END //

DELIMITER ;

Then call it:

sqlCALL GetUsersByAge(25);

24. What is a trigger?

A trigger is a stored code block that automatically executes when a specified event occurs (e.g., INSERT, UPDATE, DELETE on a table). It’s often used for audit logging, data validation, or enforcing business rules.

Example concept:

sqlCREATE TRIGGER log_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_log (user_id, action, timestamp)
    VALUES (NEW.id, 'INSERT', NOW());
END;

25. What are indexes on multiple columns?

A composite index (or multi‑column index) is created on two or more columns. The order of columns matters because the index is used from left to right.

Example:

sqlCREATE INDEX idx_name_age ON users(name, age);

This index is useful for queries that filter on both name and age, or only on name.

26. When should you avoid indexes?

You should be cautious or avoid indexes when:

• The table is very small (cost of maintaining the index may exceed benefit).
• The table is write‑heavy (frequent INSERT/UPDATE/DELETE can slow down as indexes must be updated).
• The column has many duplicate or NULL values (index may not help much).

27. What is database normalization vs denormalization?

• Normalization reduces redundancy and improves integrity by splitting data into multiple tables.
• Denormalization intentionally adds redundancy (e.g., duplicated columns) to improve read performance or simplify complex joins.

You normalize in design for clean data; sometimes denormalize later for performance.

28. How do you find duplicate rows?

Example to find duplicate emails:

sqlSELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

You can then decide how to handle duplicates (delete, merge, mark).

29. How do you delete duplicates, keeping only one row?

One common pattern (using a window function or self‑join):

Using ROW_NUMBER() (if your MySQL version supports it):

sqlDELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.email = u2.email;

This keeps the row with the smallest id and removes others.

30. What are the main types of relationships in a database?

• One‑to‑One – one row in table A links to one row in table B.
• One‑to‑Many – one row in table A links to many rows in table B (most common, e.g., user–orders).
• Many‑to‑Many – multiple rows in table A link to multiple rows in table B (handled via a junction table).

Advanced‑level MySQL interview questions

31. What are ACID properties in transactions?

ACID stands for:

• Atomicity – all operations in a transaction succeed or none do.
• Consistency – database remains in a valid state before and after the transaction.
• Isolation – concurrent transactions don’t interfere in unexpected ways.
• Durability – once a transaction is committed, changes survive crashes.

These properties are especially important in InnoDB (MySQL’s default transactional storage engine).

32. What is a transaction in MySQL?

A transaction is a sequence of SQL operations treated as a single unit of work. You usually wrap them with START TRANSACTION (or BEGIN), then COMMIT or ROLLBACK.

Example:

sqlSTART TRANSACTION;

UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

COMMIT; -- or ROLLBACK if something goes wrong

33. What is a deadlock and how does MySQL handle it?

A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a cycle. MySQL automatically detects deadlocks and rolls back one of the transactions so the others can proceed.

Best practice:

• Keep transactions short and consistent in the order of table access.
• Retry the rolled‑back transaction if your application logic allows.

34. What are isolation levels?

Isolation levels control how transactions interact with each other:

• READ UNCOMMITTED – can read uncommitted data (dirty reads).
• READ COMMITTED – see only committed data; no dirty reads.
• REPEATABLE READ – default in MySQL; ensures consistent reads within the same transaction.
• SERIALIZABLE – highest isolation; prevents most concurrency issues but can reduce performance.

Set in MySQL:

sqlSET TRANSACTION ISOLATION LEVEL READ COMMITTED;

35. What is the difference between MyISAM and InnoDB?

• InnoDB:
  • Supports transactions, row‑level locking, foreign keys.
  • Better for write‑heavy and transactional workloads.
• MyISAM:
  • Faster for read‑heavy, simple workloads.
  • No transactions; table‑level locking only.

For most modern applications, InnoDB is recommended.

36. What is replication in MySQL?

Replication copies data from a master server to one or more slave servers. It’s used for backup, load balancing reads, and high availability.

Common setup:

• Master writes data.
• Slaves replay changes from the master’s binary log.

Example concepts:

sql-- On master: enable binary logging, create replication user
-- On slave: configure master host, credentials, and start replication

37. What is a query execution plan?

The execution plan shows how MySQL will execute a query (which indexes it uses, join order, etc.). You inspect it using EXPLAIN or EXPLAIN FORMAT=JSON.

Example:

sqlEXPLAIN SELECT * FROM users WHERE name = 'Alice';

This helps you see if your indexes are effective and where bottlenecks might be.

38. How do you optimize a slow query?

Typical steps:

• Use EXPLAIN to understand the plan.
• Add or tune indexes on columns used in WHERE, JOIN, and ORDER BY.
• Avoid SELECT * in large tables; fetch only needed columns.
• Reduce complex subqueries or joins if possible.
• Consider partitioning for very large tables.

39. What is partitioning?

Partitioning splits a table into smaller, physically separate pieces (partitions) based on a rule (e.g., by date or range). It can improve query performance and manageability for huge tables.

Example concept:

sqlCREATE TABLE logs (
    id INT,
    log_date DATE
)
PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022)
);

40. What is the difference between views and stored procedures?

• Views are virtual tables defined by a query; they’re used for:
  • Readable, reusable queries.
  • Simplifying complex joins.
• Stored procedures are blocks of procedural code that can:
  • Accept parameters.
  • Contain multiple statements, loops, conditionals.
  • Be used for complex business logic.

Views are simpler and more “declarative”; stored procedures are more powerful but can be harder to maintain.

41. What is a covering index?

A covering index includes all columns needed by a query, so MySQL can answer the query directly from the index without going back to the table data. This often speeds up read performance.

Example:

sqlCREATE INDEX idx_name_age_salary ON employees(name, age, salary);

If your query only selects those three columns and filters by them, MySQL may use this index alone.

42. How do you handle large datasets efficiently?

Strategies include:

• Proper indexing on frequently queried columns.
• Partitioning very large tables (e.g., by date).
• Archiving old data to separate tables or systems.
• Using read replicas for reporting and analytics.
• Caching frequent query results in your application layer.

Here are the remaining 5 advanced‑level MySQL interview questions (Q43–Q47) with answers, plus a short conclusion section you can paste right after your existing blog content.

43. What is a materialized view conceptually (even if not native in MySQL)?

A materialized view is a view whose result is physically stored on disk and refreshed periodically, rather than recalculated every time it’s queried. MySQL doesn’t have built‑in materialized views, but you can simulate them by:

• Creating a regular table (e.g., materialized_report).
• Populating it with results from a complex query (INSERT INTO ... SELECT ...).
• Using a scheduled job (cron, stored procedure, or app‑level task) to refresh it at intervals.

This pattern is useful for dashboards or analytics that run the same expensive query over and over.

44. What is connection pooling and why does it matter?

Connection pooling is a technique where a pool of database connections is created once and reused by multiple requests instead of opening and closing a new connection for each query. In MySQL‑based applications, this is usually managed by your application framework or a middleware (like HikariCP, PgBouncer‑style tools, or ORM‑specific pools).

It matters because:

• Opening a new TCP connection and authenticating to MySQL is relatively slow.
• Holding many short‑lived connections can overload the database and exhaust its connection limit.
• A small pool of long‑lived connections is much more efficient and scalable.

45. What are prepared statements and how do they help?

A prepared statement is a SQL template that is parsed and compiled once by the database, then executed multiple times with different parameter values. In MySQL you usually use them through client libraries (JDBC, PDO, MySQLi, etc.).

Example concept (in code):

sqlPREPARE stmt FROM 'SELECT * FROM users WHERE age > ?';
SET @min_age = 25;
EXECUTE stmt USING @min_age;
DEALLOCATE PREPARE stmt;

They help by:

• Reducing parsing overhead for repeated queries.
• Preventing SQL injection (input values are never part of the SQL text).

46. What is the difference between utf8 and utf8mb4 in MySQL?

In MySQL’s character sets:

• utf8 (now internally utf8mb3) only supports up to 3‑byte UTF‑8 characters (covers most legacy text but not full Unicode).
• utf8mb4 supports up to 4‑byte UTF‑8 characters, which includes:
  • Emojis.
  • Many ideographic scripts and special symbols.

Best practice:

• Use utf8mb4 and utf8mb4_unicode_ci as your default charset and collation so that usernames, product names, and user‑generated content can safely store modern Unicode.

47. How would you design a simple e‑commerce database schema for MySQL?

At a high level, a typical e‑commerce schema includes:

• users – customer accounts (id, email, name, password_hash, created_at).
• products – product details (id, name, description, price, stock, category_id).
• categories – product categories.
• orders – orders (id, user_id, order_date, status, total_amount).
• order_items – many‑to‑many bridge (order_id, product_id, quantity, price_at_time_of_order).
• payment_logs – optional table to track payment attempts and status.

Example schema snippet:

sqlCREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100),
    created_at DATETIME DEFAULT NOW()
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    stock INT
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_date DATETIME DEFAULT NOW(),
    total_amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

This design keeps data normalized, supports queries like “all orders for a user” or “top‑selling products,” and is easy to extend with promos, reviews, or shipping tables.

Conclusion

Practicing MySQL interview questions regularly helps you transition from remembering syntax to thinking like a database designer and optimizer. Whether you’re a beginner learning your first SELECT statement or an experienced engineer handling transactions, indexing, and replication, structured practice is key.

If you combine this MySQL‑focused guide with our broader Technical Interview Questions resource, you’ll be well‑prepared not just for database rounds, but for full‑stack and backend interviews more generally.