100% Free
🎯 3200+ Questions. One Goal: Your Success Sign In Sign Up

Top MySQL Interview Questions

Curated MySQL interview questions and answers across difficulty levels.

Last updated:

MySQL Interview Questions & Answers

Skip to Questions

Welcome to our comprehensive collection of MySQL interview questions and answers. This page contains expertly curated interview questions covering all aspects of MySQL, from fundamental concepts to advanced topics. Whether you're preparing for an entry-level position or a senior role, you'll find questions tailored to your experience level.

Our MySQL interview questions are designed to help you:

  • Understand core concepts and best practices in MySQL
  • Prepare for technical interviews at all experience levels
  • Master both theoretical knowledge and practical application
  • Build confidence for your next MySQL interview

Each question includes detailed answers and explanations to help you understand not just what the answer is, but why it's correct. We cover topics ranging from basic MySQL concepts to advanced scenarios that you might encounter in senior-level interviews.

Use the filters below to find questions by difficulty level (Entry, Junior, Mid, Senior, Expert) or focus specifically on code challenges. Each question is carefully crafted to reflect real-world interview scenarios you'll encounter at top tech companies, startups, and MNCs.

Questions

110 questions
Q1:

What is MySQL?

Entry

Answer

MySQL is an open-source relational database management system that stores data in tables and uses SQL for managing and querying data.
Permalink
Q2:

What is a database table?

Entry

Answer

A table is a structured set of rows and columns used to store related data, where each row is a record and each column has a data type.
Permalink
Q3:

What is a primary key?

Entry

Answer

A primary key uniquely identifies each row in a table and cannot be NULL. It ensures uniqueness and is used for indexing and relationships.
Permalink
Q4:

What is a foreign key?

Entry

Answer

A foreign key is a column that references a primary key in another table, enforcing referential integrity between related tables.
Permalink
Q5:

What is SQL?

Entry

Answer

SQL stands for Structured Query Language and is used to insert, update, delete, and query data in relational databases.
Permalink
Q6:

What are the different types of SQL commands?

Entry

Answer

SQL commands include DDL (CREATE, ALTER, DROP), DML (INSERT, UPDATE, DELETE), DQL (SELECT), DCL (GRANT, REVOKE), TCL (COMMIT, ROLLBACK).
Permalink
Q7:

What is a constraint in MySQL?

Entry

Answer

A constraint is a rule applied to table columns to maintain data accuracy and consistency, such as PRIMARY KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.
Permalink
Q8:

What is the difference between CHAR and VARCHAR?

Entry

Answer

CHAR is fixed-length storage while VARCHAR is variable-length. VARCHAR saves space and is preferred for dynamic text.
Permalink
Q9:

What is the purpose of the SELECT statement?

Entry

Answer

SELECT retrieves data from tables and supports filtering, sorting, grouping, and joining.
Permalink
Q10:

What is a WHERE clause used for?

Entry

Answer

WHERE filters records based on conditions, allowing retrieval of only relevant rows.
Permalink
Q11:

What is ORDER BY used for?

Entry

Answer

ORDER BY sorts query results in ascending or descending order on one or more columns.
Permalink
Q12:

What is LIMIT in MySQL?

Entry

Answer

LIMIT restricts the number of rows returned by a query, commonly used for pagination.
Permalink
Q13:

What is a JOIN?

Entry

Answer

JOIN combines rows from multiple tables based on related columns such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN.
Permalink
Q14:

What is an index in MySQL?

Entry

Answer

An index improves read performance by speeding up searches but adds slight overhead to write operations.
Permalink
Q15:

What is NULL in MySQL?

Entry

Answer

NULL represents missing or unknown data. It is not equal to zero or an empty string and requires IS NULL or IS NOT NULL for checks.
Permalink
Q16:

Difference between DELETE and TRUNCATE?

Entry

Answer

DELETE removes selected rows and is logged, while TRUNCATE removes all rows quickly and resets auto-increment but cannot be rolled back in many cases.
Permalink
Q17:

What does AUTO_INCREMENT do?

Entry

Answer

AUTO_INCREMENT automatically generates sequential numeric values, commonly used for primary key columns.
Permalink
Q18:

What is a default value in MySQL?

Entry

Answer

A default value is used when no explicit value is provided during insertion, ensuring consistent data entry.
Permalink
Q19:

What is the purpose of GROUP BY?

Entry

Answer

GROUP BY groups rows based on column values and is used with aggregate functions to summarize data.
Permalink
Q20:

What is an aggregate function?

Entry

Answer

Aggregate functions calculate values over multiple rows, such as COUNT, SUM, AVG, MIN, and MAX.
Permalink
Q21:

How does MySQL’s InnoDB storage engine manage transactions internally?

Junior

Answer

InnoDB manages transactions using undo logs, redo logs, and a buffer pool. It maintains ACID compliance through MVCC, row-level locking, and transaction logs that support rollbacks and crash recovery.
Permalink
Q22:

What is MVCC (Multi-Version Concurrency Control) in MySQL?

Junior

Answer

MVCC allows multiple transactions to read and write data without blocking. Readers view snapshot data while writers update the latest version using undo logs.
Permalink
Q23:

What is the purpose of the InnoDB buffer pool?

Junior

Answer

The buffer pool caches data and index pages in memory to reduce disk I/O and improve performance, keeping frequently used data readily available.
Permalink
Q24:

What is a covering index?

Junior

Answer

A covering index includes all columns required by a query, allowing MySQL to fetch results directly from the index without accessing the table.
Permalink
Q25:

What is an execution plan and why is EXPLAIN important?

Junior

Answer

An execution plan describes how MySQL executes a query. Using EXPLAIN reveals join types, index usage, and potential performance problems.
Permalink
Q26:

What is the difference between row-level and table-level locking?

Junior

Answer

Row-level locking locks only specific rows, improving concurrency. Table-level locking locks the entire table and blocks other write operations.
Permalink
Q27:

How does MySQL handle indexing on large tables?

Junior

Answer

Indexing large tables may involve rebuilding indexes and using memory buffers. Online DDL and partitioning help avoid downtime.
Permalink
Q28:

What is a slow query log and when should you enable it?

Junior

Answer

The slow query log records queries that exceed a specified execution time. It helps identify unoptimized queries or missing indexes.
Permalink
Q29:

What is the difference between logical and physical backups?

Junior

Answer

Logical backups export SQL statements, while physical backups copy actual data files. Physical backups are faster for large datasets.
Permalink
Q30:

What is a stored routine and why use it?

Junior

Answer

Stored routines include procedures and functions that store logic on the server, reducing network usage and improving consistency.
Permalink
Q31:

How does MySQL handle foreign key constraints during deletes or updates?

Junior

Answer

Foreign keys enforce referential integrity using actions like CASCADE, RESTRICT, and SET NULL for parent-child relationships.
Permalink
Q32:

What is partitioning and when is it useful?

Junior

Answer

Partitioning splits a large table into smaller parts to improve performance, maintenance, and query speed.
Permalink
Q33:

What are FULLTEXT indexes and when are they used?

Junior

Answer

FULLTEXT indexes speed up text searching and support natural language and boolean search modes.
Permalink
Q34:

What is a deadlock detector in InnoDB?

Junior

Answer

InnoDB automatically identifies deadlocks and rolls back one transaction to allow others to proceed.
Permalink
Q35:

How do prepared statements improve performance and security?

Junior

Answer

Prepared statements precompile SQL and prevent SQL injection by separating queries from data.
Permalink
Q36:

What are triggers and their performance implications?

Junior

Answer

Triggers automate actions but add overhead to write operations, making heavy trigger use slow and harder to debug.
Permalink
Q37:

What is the difference between CHAR, VARCHAR, and TEXT in performance?

Junior

Answer

CHAR is fixed-length and fast. VARCHAR is variable-length and space-efficient. TEXT is stored separately and slower to query.
Permalink
Q38:

How do you optimize JOIN performance in MySQL?

Junior

Answer

Optimizing joins involves indexing join columns, avoiding functions on indexed fields, and analyzing EXPLAIN output.
Permalink
Q39:

How does MySQL handle replication?

Junior

Answer

Replication sends changes from a primary server to replicas using async, semi-sync, or GTID-based methods.
Permalink
Q40:

What are common reasons for slow MySQL performance?

Junior

Answer

Slow performance is often due to poor indexing, large table scans, insufficient memory, high disk I/O, or heavy locking.
Permalink
Q41:

What is the difference between INNER JOIN and LEFT JOIN?

Mid

Answer

INNER JOIN returns only matching rows between tables. LEFT JOIN returns all rows from the left table and matching rows from the right, with NULL for non-matching rows.
Permalink
Q42:

What is a composite primary key?

Mid

Answer

A composite primary key uses multiple columns together to uniquely identify a row, useful when no single column is unique.
Permalink
Q43:

What is the use of the DISTINCT keyword?

Mid

Answer

DISTINCT removes duplicate rows from the result set and is used to fetch unique values.
Permalink
Q44:

What is an alias in MySQL?

Mid

Answer

An alias temporarily renames a table or column to improve readability in complex queries.
Permalink
Q45:

What is the purpose of HAVING clause?

Mid

Answer

HAVING filters grouped records after GROUP BY, whereas WHERE filters rows before grouping.
Permalink
Q46:

What is the difference between UNION and UNION ALL?

Mid

Answer

UNION removes duplicates, UNION ALL returns all rows including duplicates. UNION requires extra processing.
Permalink
Q47:

What is a subquery?

Mid

Answer

A subquery is a query inside another query used for filtering, comparisons, or dynamic data selection.
Permalink
Q48:

What is a self join?

Mid

Answer

Self join is when a table is joined with itself, useful for hierarchical or relational comparisons.
Permalink
Q49:

What are MySQL data types?

Mid

Answer

MySQL supports numeric, string, date/time, and spatial data types. Choosing correct types improves performance.
Permalink
Q50:

What is a view?

Mid

Answer

A view is a virtual table based on a SELECT query, storing no physical data but simplifying data access.
Permalink
Q51:

What are the advantages of using views?

Mid

Answer

Views improve security, simplify complex queries, and provide logical abstraction of data.
Permalink
Q52:

What is a stored procedure?

Mid

Answer

A stored procedure is precompiled SQL code stored in the database, reducing repetition and improving performance.
Permalink
Q53:

What is a stored function?

Mid

Answer

A stored function returns a single value and can be used in SQL statements for transformations or calculations.
Permalink
Q54:

What is a trigger in MySQL?

Mid

Answer

A trigger executes automatically before or after INSERT, UPDATE, or DELETE, useful for logs or business rules.
Permalink
Q55:

What is normalization?

Mid

Answer

Normalization organizes data to reduce redundancy and improve integrity by splitting into logical tables.
Permalink
Q56:

What are the normal forms?

Mid

Answer

1NF removes repeating groups, 2NF removes partial dependencies, 3NF removes transitive dependencies.
Permalink
Q57:

What is denormalization?

Mid

Answer

Denormalization adds redundancy to improve read performance, often used in analytics and reporting.
Permalink
Q58:

What is a schema in MySQL?

Mid

Answer

A schema is another name for a database, containing tables, views, procedures, and triggers.
Permalink
Q59:

What is a transaction?

Mid

Answer

A transaction is a group of SQL operations treated as a single unit that can be committed or rolled back.
Permalink
Q60:

What are ACID properties?

Mid

Answer

ACID ensures data reliability: Atomicity, Consistency, Isolation, Durability.
Permalink
Q61:

What is a deadlock in MySQL?

Mid

Answer

A deadlock occurs when two transactions wait indefinitely for each other’s locks. MySQL resolves it by rolling back one.
Permalink
Q62:

What is index cardinality?

Mid

Answer

Cardinality measures uniqueness of indexed values. High cardinality indexes improve search speed.
Permalink
Q63:

What is a clustered index?

Mid

Answer

In InnoDB, the primary key creates a clustered index, storing rows physically in key order.
Permalink
Q64:

What is a non-clustered index?

Mid

Answer

A non-clustered index stores pointers to table rows and is used on non-primary key columns.
Permalink
Q65:

What is the difference between DELETE and DROP?

Mid

Answer

DELETE removes rows, whereas DROP removes the table structure entirely.
Permalink
Q66:

What is ENUM data type?

Mid

Answer

ENUM restricts a column to a predefined list of values, ensuring consistent data input.
Permalink
Q67:

What is IN operator?

Mid

Answer

IN checks if a value exists within a list of values and simplifies conditional queries.
Permalink
Q68:

What is BETWEEN operator?

Mid

Answer

BETWEEN checks if a value lies within a range, commonly used with dates and numbers.
Permalink
Q69:

What are aggregate functions?

Mid

Answer

Aggregate functions compute results from sets of rows such as COUNT, SUM, AVG, MIN, and MAX.
Permalink
Q70:

What are scalar functions?

Mid

Answer

Scalar functions operate on individual values such as UPPER, LOWER, CONCAT, and LENGTH.
Permalink
Q71:

What is a join condition?

Mid

Answer

A join condition defines how tables relate, usually through primary and foreign key columns.
Permalink
Q72:

What is a cross join?

Mid

Answer

A cross join returns the Cartesian product of two tables.
Permalink
Q73:

What is a natural join?

Mid

Answer

A natural join automatically matches columns with the same name, which can be risky if unintended matches occur.
Permalink
Q74:

How does MySQL handle NULL values in comparison?

Mid

Answer

NULL does not equal anything, even another NULL. Use IS NULL or IS NOT NULL for comparisons.
Permalink
Q75:

What is a temporary table?

Mid

Answer

A temporary table exists only for the session and is deleted automatically when the session ends.
Permalink
Q76:

Difference between COUNT(*) and COUNT(column)?

Mid

Answer

COUNT(*) counts all rows including NULLs, while COUNT(column) counts only non-null values.
Permalink
Q77:

Join vs Subquery — when to use which?

Mid

Answer

JOINs are faster and used to combine related tables. Subqueries help structure logic but may be slower.
Permalink
Q78:

Difference between DATETIME and TIMESTAMP?

Mid

Answer

DATETIME stores exact values independent of timezone; TIMESTAMP converts values based on timezone and supports auto-update.
Permalink
Q79:

Difference between MyISAM and InnoDB engines?

Mid

Answer

MyISAM is faster for reads but lacks transactions and foreign keys. InnoDB supports row-level locking and ACID transactions.
Permalink
Q80:

What is referential integrity?

Mid

Answer

Referential integrity ensures foreign keys always refer to valid rows, preventing orphaned records.
Permalink
Q81:

How does MySQL’s InnoDB engine implement MVCC internally?

Senior

Answer

InnoDB uses undo logs to store old row versions. Each transaction reads based on its snapshot ID, enabling non-blocking reads while writers modify the latest row version.
Permalink
Q82:

How does MySQL determine the best execution plan for a query?

Senior

Answer

The optimizer evaluates table statistics, cardinality, join orders, and cost estimates. It chooses the lowest I/O and CPU cost plan, relying heavily on accurate table statistics.
Permalink
Q83:

What is the difference between index scan and table scan?

Senior

Answer

Table scans read every row, while index scans use index trees to quickly locate matching rows. Index scans improve performance when filtering selective values.
Permalink
Q84:

What causes index fragmentation and how do you fix it?

Senior

Answer

Frequent updates and deletes leave gaps inside index pages. Use OPTIMIZE TABLE or rebuild indexes to reorganize and compact index pages.
Permalink
Q85:

What is a B-Tree index and how does MySQL use it?

Senior

Answer

B-Tree indexes maintain sorted keys allowing fast lookups, prefix scans, and range queries. They underpin most MySQL indexing except FULLTEXT and spatial indexes.
Permalink
Q86:

Why is column order important in composite indexes?

Senior

Answer

MySQL uses composite indexes left-to-right. If the first column is not used in filtering, the index cannot be used efficiently.
Permalink
Q87:

How does MySQL detect and resolve deadlocks?

Senior

Answer

InnoDB builds a waits-for graph and checks for cycles. When detected, it rolls back the cheapest transaction to break the deadlock.
Permalink
Q88:

How does MySQL handle high-concurrency environments?

Senior

Answer

MySQL uses row-level locks, MVCC, adaptive hash indexing, and buffer pool optimizations. Proper schema design and indexing reduce contention.
Permalink
Q89:

What is the difference between shared locks and exclusive locks?

Senior

Answer

Shared locks permit concurrent reads, while exclusive locks block all reads and writes. Writes always require exclusive locks.
Permalink
Q90:

What is a redo log and why is it important?

Senior

Answer

Redo logs store committed changes before flushing to disk. They guarantee durability and allow crash recovery.
Permalink
Q91:

What is a binlog and how does it differ from redo logs?

Senior

Answer

Binlogs store logical SQL changes for replication. Redo logs store physical page changes for recovery. They serve distinct purposes.
Permalink
Q92:

How do you design MySQL for horizontal scalability?

Senior

Answer

Use sharding, replicas, partitioning, and stateless application logic. Avoid cross-shard joins and rely on denormalized or distributed data models.
Permalink
Q93:

What is the purpose of GTID in MySQL replication?

Senior

Answer

GTIDs uniquely identify each transaction and simplify failover, resynchronization, and replication consistency.
Permalink
Q94:

What is semi-synchronous replication?

Senior

Answer

Semi-sync replication requires at least one replica to acknowledge before the primary commits, providing improved durability.
Permalink
Q95:

What causes replication lag and how do you detect it?

Senior

Answer

Lag occurs when replicas apply changes slower than the primary. Use Seconds_Behind_Master and check slow queries or overloaded replica hardware.
Permalink
Q96:

What is the difference between statement-based and row-based replication?

Senior

Answer

Statement-based logs SQL text while row-based logs changed values. Row-based avoids inconsistencies but increases log size.
Permalink
Q97:

How do you handle inconsistent replica data?

Senior

Answer

Use checksums, pt-table-sync, or resync using GTID. In severe cases, recreate the replica.
Permalink
Q98:

How do you design indexes for large OLTP systems?

Senior

Answer

Use highly selective, narrow, composite indexes. Avoid indexing low-cardinality columns and review real query patterns regularly.
Permalink
Q99:

What is query profiling in MySQL?

Senior

Answer

Query profiling breaks execution into detailed stages. It helps locate time-consuming operations like sorting or copying.
Permalink
Q100:

How does MySQL handle sorting and temporary tables?

Senior

Answer

MySQL sorts using in-memory temp tables when possible and switches to disk-based files if data exceeds memory limits.
Permalink
Q101:

What is the difference between HASH and B-Tree indexes?

Senior

Answer

HASH indexes support equality lookups and are used in MEMORY tables. B-Tree indexes support ranges, sorting, and prefix lookups.
Permalink
Q102:

How do you optimize JOIN performance in multi-table queries?

Senior

Answer

Index join keys, simplify join order, avoid functions on columns, and use covering indexes to reduce table lookups.
Permalink
Q103:

Why can functions in WHERE clause hurt performance?

Senior

Answer

Functions prevent MySQL from using indexes because they alter column values, forcing full scans.
Permalink
Q104:

How do you handle large DELETE operations efficiently?

Senior

Answer

Delete in batches, drop partitions instead of deleting rows, or disable constraints if safe. This avoids long locks and table bloat.
Permalink
Q105:

Why was the MySQL query cache removed in version 8?

Senior

Answer

Query cache caused severe contention and invalidation overhead under concurrency. MySQL 8 removed it for scalability reasons.
Permalink
Q106:

What is the importance of ANALYZE TABLE?

Senior

Answer

ANALYZE TABLE refreshes statistics so the optimizer can choose efficient execution plans.
Permalink
Q107:

What happens internally during an UPDATE statement?

Senior

Answer

MySQL locks the row, updates memory pages, logs changes to redo logs, modifies undo logs for MVCC, and writes binlog entries.
Permalink
Q108:

What is a gap lock in InnoDB?

Senior

Answer

Gap locks block inserts in a range. They enforce REPEATABLE READ isolation and reduce phantom reads but can increase contention.
Permalink
Q109:

Why is REPEATABLE READ default in MySQL?

Senior

Answer

It provides strong consistency while relying on MVCC snapshots to avoid heavy locking and phantom reads.
Permalink
Q110:

How do you tune MySQL for high-write workloads?

Senior

Answer

Increase buffer pool size, tune redo logs, minimize secondary indexes, optimize flush frequency, use batching, and partition large tables.
Permalink

Curated Sets for MySQL

No curated sets yet. Group questions into collections from the admin panel to feature them here.

People Also Ask - Related MySQL Questions

What is MySQL?
What is a database table?
What is a primary key?
What is a foreign key?
What is SQL?
What are the different types of SQL commands?
Ready to level up? Start Practice