{jsonbody.com}

Top 200 MySQL Interview Questions and Answers

Master database and backend interviews with our ultimate collection of top 200 MySQL interview questions, storage engines, indexing, queries, and expert answers.

Q1. What is MySQL?

MySQL is an open-source relational database management system (RDBMS) based on Structured Query Language (SQL), known for reliability, speed, and ease of use.

Q2. What is an RDBMS?

A Relational Database Management System is a database management system that stores data in tables with rows and columns, establishing relationships via foreign keys.

Q3. What are the storage engines available in MySQL?

InnoDB (default, supports transactions and foreign keys), MyISAM (non-transactional, fast reads), Memory, CSV, Archive, and Merge.

Q4. What is the difference between InnoDB and MyISAM?

InnoDB supports ACID transactions, row-level locking, and foreign keys. MyISAM supports table-level locking only, does not support transactions or foreign keys, but can be faster for pure read-heavy select operations.

Q5. What is a Primary Key?

A constraint that uniquely identifies each record in a database table. It cannot contain NULL values and must contain unique values.

Q6. What is a Foreign Key?

A field or group of fields in one table that uniquely identifies a row of another table, establishing a parent-child relationship between tables.

Q7. What is a Unique Key?

A constraint ensuring that all values in a column are unique, allowing a single NULL value depending on the database system.

Q8. What is a Composite Key?

A primary key composed of two or more columns used together to uniquely identify a table row.

Q9. What is normalization?

The process of organizing data in a database to reduce redundancy and improve data integrity, dividing large tables into smaller related tables.

Q10. What are the Normal Forms in SQL?

1NF (Atomic values), 2NF (1NF + no partial functional dependencies), 3NF (2NF + no transitive dependencies), and BCNF (Boyce-Codd Normal Form).

Q11. What is denormalization?

The strategy of introducing redundancy into normalized tables to improve read query performance by avoiding expensive joins.

Q12. What is a SQL Join?

An operation used to combine rows from two or more tables based on a related column between them.

Q13. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only records that have matching values in both tables. LEFT JOIN returns all records from the left table, and the matched records from the right table (with NULLs if no match).

Q14. What is a RIGHT JOIN?

Returns all records from the right table, and the matched records from the left table.

Q15. What is a FULL OUTER JOIN?

Returns all records when there is a match in either left or right table records. (Note: MySQL simulates FULL OUTER JOIN using UNION of LEFT and RIGHT joins).

Q16. What is a CROSS JOIN?

Produces a Cartesian product of the two tables, returning every possible combination of rows from both tables.

Q17. What is a SELF JOIN?

A regular join where a table is joined with itself, useful for hierarchical or recursive data relationships.

Q18. What is an Index in MySQL?

A data structure (like B-Tree) that improves the speed of data retrieval operations on a database table at the cost of writes.

Q19. What is a Clustered Index?

An index that defines the physical order of data rows in a table. InnoDB tables use the primary key as the clustered index.

Q20. What is a Non-Clustered Index (Secondary Index)?

An index structure separate from the data rows that contains index key values and pointers pointing back to the data rows.

Q21. What is a Composite Index?

An index created on multiple columns, following the Leftmost Prefix Rule for query optimization.

Q22. What is a Unique Index?

An index that ensures duplicate key values are not permitted in the indexed columns.

Q23. What is Full-Text Index?

An index optimized for natural language text searches across text columns.

Q24. What is EXPLAIN in MySQL?

A diagnostic statement that displays how MySQL executes a query, helping analyze execution plans and missing indexes.

Q25. What is a View in MySQL?

A virtual table based on the result-set of an SQL statement, containing rows and columns just like a real table.

Q26. What is a Materialized View?

Unlike standard views, materialized views store physical query results on disk (MySQL simulates this via summary tables or triggers).

Q27. What is a Stored Procedure?

A prepared SQL code block that can be saved and reused across applications, executing server-side logic.

Q28. What is a Stored Function?

Similar to stored procedures, but must always return a single value and can be used inside SQL statements.

Q29. What is a Trigger?

A database procedure that automatically executes in response to specific events (INSERT, UPDATE, DELETE) on a table.

Q30. What are ACID properties?

Atomicity, Consistency, Isolation, and Durability: properties guaranteeing reliable database transaction processing.

Q31. What is Atomicity in ACID?

Ensures all operations within a transaction complete successfully; if one fails, the entire transaction is rolled back.

Q32. What is Consistency in ACID?

Ensures a transaction brings the database from one valid state to another, maintaining all predefined rules and constraints.

Q33. What is Isolation in ACID?

Ensures concurrent transactions execute independently without interfering with each other.

Q34. What is Durability in ACID?

Ensures once a transaction is committed, its changes persist permanently even during system crashes.

Q35. What are Transaction Isolation Levels?

Read Uncommitted, Read Committed, Repeatable Read (default in InnoDB), and Serializable.

Q36. What is Dirty Read?

Occurs when a transaction reads data written by another uncommitted transaction.

Q37. What is Non-Repeatable Read?

Occurs when a transaction reads the same row twice and gets different values because another transaction modified and committed updates in between.

Q38. What is Phantom Read?

Occurs when a transaction executes a query twice returning a set of rows satisfying a condition, but another transaction inserts new matching rows in between.

Q39. What is MVCC?

Multi-Version Concurrency Control: a concurrency architecture used by InnoDB to store multiple versions of data rows for consistent non-blocking reads.

Q40. What is Deadlock?

A situation where two or more transactions hold locks on resources and wait indefinitely for each other to release them.

Q41. How does MySQL handle deadlocks?

InnoDB automatically detects deadlocks and rolls back the transaction with the fewest locks (victim).

Q42. What is SQL injection?

A vulnerability where malicious SQL statements are inserted into entry fields for execution.

Q43. How do you prevent SQL injection in MySQL?

By using prepared statements, parameterized queries, and input sanitization.

Q44. What is the difference between WHERE and HAVING?

WHERE filters rows before aggregation occurs. HAVING filters groups after aggregation is completed.

Q45. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes rows individually and logs each in undo logs (can be rolled back). TRUNCATE resets table data quickly without row-level logging. DROP completely deletes the table structure and data from disk.

Q46. What is the difference between COUNT(col), COUNT(*), and COUNT(1)?

COUNT(*) counts all matching table rows. COUNT(col) counts rows where col is not NULL. COUNT(1) evaluates constant 1 per row, equivalent in performance to COUNT(*).

Q47. What is LIMIT clause used for?

To restrict the number of records returned by a SELECT query.

Q48. What is OFFSET in MySQL?

Used alongside LIMIT to skip a specified number of initial records before returning results.

Q49. What is COALESCE function?

Returns the first non-null expression in a list of arguments.

Q50. What is IFNULL function?

Returns a specified alternative value if an expression evaluates to NULL.

Q51. What is NULLIF function?

Returns NULL if two expressions are equal; otherwise returns the first expression.

Q52. What is CASE statement in SQL?

Provides conditional branching logic within SQL queries (IF-THEN-ELSE behavior).

Q53. What are aggregate functions?

Functions performing calculations across multiple rows to return a single summary value (SUM, AVG, MIN, MAX, COUNT).

Q54. What is GROUP BY clause?

Groups rows sharing common values into summary rows.

Q55. What is Subquery?

A query nested inside another SQL statement (e.g., inside WHERE, FROM, or SELECT).

Q56. What is Correlated Subquery?

A subquery that depends on values from the outer query, executing once for each row evaluated by the outer query.

Q57. What is Common Table Expression (CTE)?

A temporary named result set defined within the execution scope of a single statement using WITH syntax.

Q58. What is Recursive CTE?

A CTE that calls itself, useful for traversing hierarchical tree structures or organizational charts.

Q59. What is Window Function in MySQL?

Performs calculations across a set of table rows related to the current row (e.g., ROW_NUMBER(), RANK(), SUM() OVER()).

Q60. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

ROW_NUMBER assigns unique sequential integers. RANK assigns identical ranks for tie values with gaps in sequence. DENSE_RANK assigns identical ranks without gaps.

Q61. What is UNION vs UNION ALL?

UNION combines result sets and removes duplicate rows. UNION ALL combines result sets including all duplicates, making it faster.

Q62. What is INTERSECT and EXCEPT in SQL?

INTERSECT returns common rows between two queries. EXCEPT returns rows from the first query not present in the second.

Q63. What is explicit vs implicit locking?

Explicit locking locks rows intentionally using SELECT ... FOR UPDATE. Implicit locking is managed automatically by MySQL during writes.

Q64. What is optimistic vs pessimistic locking?

Pessimistic locking locks records during reads to prevent updates. Optimistic locking assumes minimal conflicts, validating row versions upon updates.

Q65. What is query cache?

A deprecated MySQL feature that stored query result sets in memory (removed in MySQL 8.0 due to scalability bottlenecks).

Q66. What is InnoDB Buffer Pool?

The main memory area where InnoDB caches table data and index pages, crucial for MySQL performance.

Q67. What is Redo Log?

A disk-based data structure used during crash recovery to ensure durability of committed transactions.

Q68. What is Undo Log?

Stores old versions of data rows to support rollbacks and MVCC consistent reads.

Q69. What is Binary Log (Binlog)?

Logs all statements updating database data, essential for replication and point-in-time recovery.

Q70. What is Slow Query Log?

A diagnostic log recording queries that take longer than long_query_time seconds to execute.

Q71. What is replication in MySQL?

The process of copying data from one MySQL database server (master/primary) to one or more servers (slaves/replicas).

Q72. What is asynchronous vs semi-synchronous replication?

Asynchronous replication lets master commit writes without waiting for slaves. Semi-synchronous replication ensures at least one slave receives and logs the transaction before master commits.

Q73. What is Group Replication?

A MySQL plugin providing high-availability fault-tolerant multi-master replication clusters.

Q74. What is sharding in MySQL?

Horizontal partitioning distributing database table rows across multiple independent servers.

Q75. What is vertical partitioning?

Splitting table columns into separate tables to minimize row size and optimize disk I/O.

Q76. What is horizontal partitioning?

Splitting table rows into separate tables based on range or hash rules.

Q77. What is MySQL Workbench?

The official visual tool for database architects, developers, and administrators.

Q78. What is mysqldump?

A command-line utility used for generating SQL backup dumps of MySQL databases.

Q79. What is mysqlpump?

A faster multi-threaded backup utility introduced as an alternative to mysqldump.

Q80. What is mysqlhotcopy?

A fast backup script specifically for MyISAM and Archive tables.

Q81. What is collation in MySQL?

A set of rules for comparing characters in a character set (e.g., utf8mb4_unicode_ci vs utf8mb4_general_ci).

Q82. What is character set?

Defines the supported pool of symbols and letters (e.g., utf8mb4).

Q83. What is stored procedure parameter types?

IN (input), OUT (output), and INOUT (both input and output).

Q84. How do you handle errors in stored procedures?

Using DECLARE ... HANDLER statements to capture SQL exceptions.

Q85. What is a cursor in stored procedures?

A mechanism enabling traversal over records returned by a query result set row by row.

Q86. What is table locking vs row locking?

Table locking locks an entire table, blocking other writers/readers. Row locking (InnoDB) locks only specific rows.

Q87. What is gap locking?

An InnoDB index record lock that locks the gap between index records to prevent phantom reads.

Q88. What is next-key locking?

A combination of record locks and gap locks used by InnoDB.

Q89. What is table optimization command?

OPTIMIZE TABLE: reclaims unused space and defragments data files after extensive deletes/updates.

Q90. What is table analysis command?

ANALYZE TABLE: analyzes and stores key distribution statistics for query optimization.

Q91. What is check table command?

CHECK TABLE: checks tables for errors and corruption.

Q92. What is repair table command?

REPAIR TABLE: repairs corrupted tables (primarily for MyISAM).

Q93. What is connection timeout configuration?

Configuring wait_timeout and interactive_timeout settings to drop idle connections.

Q94. What is max_connections?

Limits the maximum simultaneous client connections allowed by the MySQL server.

Q95. What is SQL_MODE?

Defines SQL syntax rules and data validation checks enforced by MySQL (e.g., STRICT_TRANS_TABLES).

Q96. What is JSON data type support in MySQL?

MySQL supports native JSON document storage, indexing, and querying functions starting from MySQL 5.7.

Q97. What is GENERATED COLUMNS?

Columns whose values are computed automatically from expressions or other columns (stored or virtual).

Q98. What is partition pruning?

An optimization technique where MySQL query optimizer skips scanning partitions irrelevant to the WHERE clause.

Q99. What is performance_schema?

A database monitoring instrumentation feature inspecting server execution statistics at runtime.

Q100. What is sys schema?

A collection of views and procedures helping DBAs interpret performance_schema metrics easily.

Q101. What is index cardinality?

An estimate of the number of unique values in an index column used by the optimizer.

Q102. Why avoid SELECT * in production queries?

It fetches unnecessary columns, increases network bandwidth, bypasses covered index optimizations, and wastes buffer pool cache memory.

Q103. What is index prefix indexing?

Indexing only the first N characters of a string column to save index space.

Q104. What is index selectivity?

Ratio of distinct values to total row count; higher selectivity improves index efficiency.

Q105. What is a covering index query?

A query where all selected columns are present in an index, eliminating table lookups.

Q106. What is query refactoring?

Rewriting inefficient queries (e.g., replacing OR with UNION, eliminating subqueries with JOINs).

Q107. How do you optimize pagination for large tables?

Using keyset pagination (seek method with WHERE id > last_seen_id) instead of expensive OFFSET limits.

Q108. What is a Cartesian product hazard?

Accidentally joining tables without proper ON or WHERE conditions, multiplying row counts exponentially.

Q109. What is table aliasing?

Assigning temporary shorthand names to tables in queries for readability and self-joins.

Q110. What is database sharding strategy?

Partitioning datasets across multiple database nodes based on tenant IDs, geographic regions, or hash ranges.

Q111. What is master-slave replication lag?

Delay in applying binary log updates on replicas, leading to stale read consistency.

Q112. How do you handle master-slave read-after-write consistency?

Routing critical post-write reads directly to the master node or using session replication position tracking.

Q113. What is InnoDB adaptive hash index?

An automatic memory structure accelerating frequent index lookups in InnoDB.

Q114. What is doublewrite buffer?

A storage area ensuring safe page writes before flushing data blocks to disk files, preventing partial page corruption.

Q115. What is thread cache in MySQL?

Pool of cached threads reused for incoming connections to avoid thread creation overhead.

Q116. What is table definition cache?

Caches table metadata definitions in memory to accelerate file opening operations.

Q117. What is temporary table storage engine in MySQL 8?

TempTables use memory (TempTable storage engine) or disk storage (InnoDB) for intermediate query results.

Q118. What is implicit type conversion penalty?

When query filters compare columns against mismatched data types (e.g., string vs integer), causing MySQL to ignore index scans.

Q119. What is function-based indexing workaround?

In older MySQL versions, creating functional columns or generated columns to index expression results.

Q120. What is spatial indexing (GIS)?

Indexing geographic coordinate data (POINT, POLYGON) using R-Tree spatial indexes.

Q121. What is point-in-time recovery (PITR)?

Restoring a full database backup and playing back binary logs up to an exact timestamp before a failure.

Q122. What is table checksum?

Calculates checksum algorithms to verify table data integrity.

Q123. What is database warm-up?

Preloading buffer pools or cache tables into RAM after database restarts to eliminate cold start latency.

Q124. What is audit logging plugin?

Enterprise security extension logging user connections, queries, and administrative events.

Q125. What is password validation plugin?

Enforces strict password complexity rules for database user accounts.

Q126. What is user privilege management?

Granting granular permissions using GRANT and REVOKE statements.

Q127. What is role-based access control (RBAC) in MySQL?

Creating roles, assigning privileges to roles, and granting roles to database users.

Q128. What is SSL/TLS encryption for MySQL connections?

Encrypting network traffic between client applications and MySQL servers.

Q129. What is table encryption at rest?

Encrypting InnoDB table space files on disk using master encryption keys.

Q130. What is anonymous block execution in MySQL?

MySQL does not support anonymous blocks like PL/SQL; scripts require stored procedures or client-side execution.

Q131. What is the difference between DATETIME and TIMESTAMP?

DATETIME stores dates and times without timezone awareness (fixed 5 bytes). TIMESTAMP stores epoch timestamps converted to UTC based on session timezones (4 bytes, range up to 2038).

Q132. What is YEAR data type?

Stores 2-digit or 4-digit year values.

Q133. What is ENUM data type?

A string object type with a value chosen from a permitted list of enumeration strings defined during table creation.

Q134. What is SET data type?

A string object that can have zero or more values chosen from a predefined list.

Q135. What is BLOB and TEXT storage?

Large object data types storing binary data (BLOB) or character text strings (TEXT) off-page.

Q136. What is auto_increment lock mode?

Configures how auto-increment primary keys acquire locks during inserts (traditional, consecutive, interleaved).

Q137. What is sql_safe_updates mode?

Prevents executing UPDATE or DELETE statements without a WHERE clause containing key columns.

Q138. What is optimizer hint?

Directives passed inside queries to influence query planner decisions (e.g., USE INDEX, FORCE INDEX).

Q139. What is invisible index?

An index maintained by MySQL but ignored by the optimizer, useful for testing index removal safety.

Q140. What is descending index support?

MySQL 8.0 natively supports DESC indexes in B-Trees, improving sorting performance.

Q141. What is instant DDL operations in MySQL 8?

Adding columns instantly without rewriting the entire table structure or blocking writes.

Q142. What is atomic DDL?

Ensures data dictionary updates, InnoDB operations, and binlog writes for DDL statements commit atomically.

Q143. What is data dictionary in MySQL 8?

Transactional relational data dictionary storing metadata inside InnoDB tables instead of metadata files.

Q144. What is hash join in MySQL 8?

A join method used when indexes are missing, building in-memory hash tables for equijoins.

Q145. What is common table expressions recursive depth limit?

Controlled via cte_max_recursion_depth configuration.

Q146. What is window frame clause?

Defines subset rows for window function calculations (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Q147. What is JSON table extraction?

JSON_TABLE() function converting JSON data structures into relational table columns.

Q148. What is multi-source replication?

Allows a single slave replica to receive updates from multiple master servers simultaneously.

Q149. What is GTID (Global Transaction Identifier)?

Unique identifier assigned to each transaction generated on master, simplifying replica tracking and failovers.

Q150. What is clone plugin in MySQL 8?

Enables provisioning new replica nodes directly over the network by cloning active InnoDB data.

Q151. What is thread pool plugin (Enterprise)?

Optimizes CPU utilization for thousands of concurrent connections using worker thread pools.

Q152. What is query rewrite plugin?

Intercepts and rewrites SQL queries before execution on the server.

Q153. What is memcached integration plugin?

Allows accessing InnoDB tables directly through the memcached protocol bypassing SQL parser.

Q154. What is connection attribute logging?

Stores client application metadata (like OS user or application name) in performance_schema.

Q155. What is table sampling clause?

Supports fetching random sample rows from tables (TABLESAMPLE SYSTEM(n)).

Q156. What is regex functions in MySQL?

REGEXP_LIKE, REGEXP_REPLACE, and REGEXP_SUBSTR for advanced regular expression parsing.

Q157. What is UUID generation functions?

UUID() and UUID_TO_BIN() / BIN_TO_UUID() for optimized binary UUID storage.

Q158. What is locking reads with NOWAIT and SKIP LOCKED?

Modifiers for SELECT ... FOR UPDATE instructing locks to fail immediately or skip locked rows.

Q159. What is lateral derived tables?

Allows derived tables (subqueries in FROM clause) to reference columns from preceding tables in the same FROM clause.

Q160. What is resource groups management?

Assigns server threads to specific CPU cores and thread priorities.

Q161. What is explain analyze statement?

Executes a query and displays actual execution timings alongside estimated costs.

Q162. What is binlog encryption?

Encrypts binary log files on disk to protect sensitive data transfers.

Q163. What is undo tablespace management?

Supports dynamic creation and truncation of undo tablespaces to reclaim disk space.

Q164. What is redo log capacity auto-sizing?

Dynamically manages redo log file sizing based on workload demands.

Q165. What is connection rate limiting?

Throttling incoming client connection rates to mitigate brute-force attacks.

Q166. What is administrative port?

Dedicated TCP port reserved exclusively for DBAs to connect when main max_connections is saturated.

Q167. What is SET PERSIST command?

Persists runtime configuration variable changes into mysqld-auto.cnf across server restarts.

Q168. What is SHOW PROFILE and SHOW PROFILES?

Diagnostic commands showing resource usage per query (deprecated in favor of performance_schema).

Q169. What is check constraint support?

Enforces boolean conditional rules on column values across all storage engines (introduced in MySQL 8.0.16).

Q170. What is functional index support?

Indexing the results of expression functions directly in B-Trees.

Q171. What is role default roles assignment?

Configuring default active roles automatically upon user login.

Q172. What is password history and reuse restriction?

Prevents users from reusing recent historical passwords.

Q173. What is account locking policy?

Automatically locks user accounts after successive failed login attempts.

Q174. What is enterprise firewall plugin?

Whitelists permitted SQL statements per user account to block unauthorized query executions.

Q175. What is parallel replication workers?

Configures multi-threaded slave replica processing by database or transaction groups.

Q176. What is clone local data directory provisioning?

Cloning active database states locally for replica seeding.

Q177. What is persistent statistics for query optimizer?

Saves table distribution statistics in persistent tables across restarts to maintain consistent query plans.

Q178. What is index condition pushdown (ICP)?

An optimization where storage engine evaluates index condition filters before reading full rows from table.

Q179. What is multi-range read (MRR) optimization?

Optimizes secondary index range scans by sorting row IDs sequentially before accessing disk.

Q180. What is batched key access (BKA)?

Combines MRR and hash joins to optimize join performance across foreign key relationships.

Q181. What is semi-join optimization?

Transforms IN and EXISTS subqueries into efficient semi-join executions.

Q182. What is derived table merge optimization?

Merges inline views / derived subqueries directly into outer query blocks.

Q183. What is loose index scan?

An index scanning method used in GROUP BY queries without scanning every index record.

Q184. What is tight index scan?

Standard index scan traversing index range intervals.

Q185. What is range scan optimization?

Evaluating index ranges for comparison operators like BETWEEN, >, <.

Q186. What is index merge optimization?

Combines multiple secondary index scans using intersection or union logic.

Q187. What is table scan fallback?

Full table scan execution when no indexes match query predicate filters.

Q188. What is buffer pool chunk resizing?

Dynamically resizing InnoDB buffer pool memory allocations online.

Q189. What is buffer pool dump and restore?

Saves buffer pool page state on shutdown and reloads pages on startup.

Q190. What is page cleaner threads?

Dedicated background threads flushing dirty pages from buffer pool to disk.

Q191. What is master thread coordination in InnoDB?

Manages background tasks like flushing logs and purging undo history records.

Q192. What is purge thread parallelism?

Multi-threaded purge operations cleaning up obsolete MVCC undo log records.

Q193. What is adaptive flushing?

Dynamically adjusts dirty page flush rates based on redo log generation speed.

Q194. What is flush neighbors setting?

Controls whether flushing a dirty page also flushes adjacent pages in the extent.

Q195. What is IO capacity configuration?

Informs MySQL storage subsystem of available IOPS limits for disk read/write throttling.

Q196. What is strict DDL mode?

Rejects invalid DDL statements instead of issuing warnings.

Q197. What is silent column truncation handling?

In strict mode, MySQL rejects data insertions exceeding column widths instead of truncating silently.

Q198. What is zero date handling policy?

Controls whether MySQL permits '0000-00-00' calendar dates.

Q199. What is group_concat_max_len setting?

Limits maximum string length returned by GROUP_CONCAT aggregate functions.

Q200. What is the future outlook of MySQL?

Continued performance enhancements in MySQL 8.4 LTS and future releases, deeper cloud-native integration, enhanced security compliance, and advanced JSON/vector indexing features.