MySQL Interview Cheat Sheet
Top 50 interview questions with concise answers. Print this page or save as PDF for offline study.
1. What is MySQL?
MySQL is an open-source relational database management system (RDBMS). It stores data in tables with rows and columns, uses SQL for querying, and supports ACID transactions (with InnoDB engine). Widely used for web applications - powers WordPress, Facebook (historically), and countless other apps. Owned by Oracle. MariaDB is the popular open-source fork.
2. What is a database table?
A database table organizes data into rows and columns. Each column has a defined name and data type (INT, VARCHAR, DATE). Each row is one record. Tables have a structure (schema) that all rows follow. A database contains multiple tables. Related tables are connected via foreign keys. Think of it as a spreadsheet with strict data types and constraints.
3. What is a primary key?
Primary key uniquely identifies each row in a table. Must be unique and NOT NULL. A table can have only one primary key (but it can be a composite of multiple columns). MySQL creates a clustered index on the primary key (InnoDB) - rows are stored in primary key order. Use an integer auto_increment column as primary key for best performance in InnoDB.
4. What is a foreign key?
Foreign key links one table to another, enforcing referential integrity. The FK column in the child table must match a value in the referenced (parent) table's primary/unique key or be NULL. InnoDB enforces FK constraints on INSERT, UPDATE, and DELETE. ON DELETE CASCADE, ON DELETE SET NULL, or ON DELETE RESTRICT control behavior when the parent row is deleted.
5. What is SQL?
SQL (Structured Query Language) is the standard language for interacting with relational databases. DML: SELECT (read), INSERT, UPDATE, DELETE. DDL: CREATE, ALTER, DROP (schema changes). DCL: GRANT, REVOKE (permissions). TCL: COMMIT, ROLLBACK, SAVEPOINT (transactions). All major RDBMS support SQL with minor variations.
6. What are the different types of SQL commands?
SQL command types: DDL (Data Definition Language) - CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE. DML (Data Manipulation Language) - SELECT, INSERT, UPDATE, DELETE. DCL (Data Control Language) - GRANT, REVOKE permissions. TCL (Transaction Control Language) - COMMIT, ROLLBACK, SAVEPOINT. Each type serves a different purpose in database management.
7. What is a constraint in MySQL?
Constraints enforce rules on data in tables. Types: PRIMARY KEY (unique + not null), FOREIGN KEY (referential integrity), UNIQUE (no duplicates), NOT NULL (required field), CHECK (custom condition on value - MySQL 8.0.16+), DEFAULT (fallback value on insert). Constraints protect data integrity at the database level, not just the application level.
8. What is the difference between CHAR and VARCHAR?
CHAR(n): fixed-length string - always stores exactly n bytes, padded with spaces. Fast for fixed-length data (country codes, status flags). VARCHAR(n): variable-length string - stores actual length + 1-2 length bytes. More space-efficient for varying lengths. CHAR is slightly faster to read; VARCHAR saves space. Use CHAR for truly fixed-length values, VARCHAR for everything else.
9. What is the purpose of the SELECT statement?
SELECT retrieves data from one or more tables. Basic: SELECT col1, col2 FROM table_name WHERE condition. SELECT * fetches all columns. Use aliases: SELECT name AS full_name. Combine with JOIN for multiple tables, GROUP BY for aggregation, ORDER BY for sorting, LIMIT for pagination. It's the most used SQL statement and the foundation of all data retrieval.
10. What is a WHERE clause used for?
WHERE filters rows before they are returned or affected. Only rows matching the condition are processed. Used with SELECT, UPDATE, and DELETE. Conditions: comparison (=, >, <, !=), logical (AND, OR, NOT), pattern (LIKE "%term%"), NULL checks (IS NULL, IS NOT NULL), ranges (BETWEEN x AND y), sets (IN (1,2,3)). WHERE runs before GROUP BY; use HAVING to filter after aggregation.
11. What is ORDER BY used for?
ORDER BY sorts query results. ASC (ascending, default) or DESC (descending). Sort by multiple columns: ORDER BY last_name ASC, first_name ASC. Without ORDER BY, MySQL makes no guarantee about row order - never assume rows come back in insert order. ORDER BY can sort by column position (ORDER BY 2) but this is fragile. Always use column names for clarity.
12. What is LIMIT in MySQL?
LIMIT restricts how many rows are returned: SELECT * FROM users LIMIT 10. LIMIT with OFFSET for pagination: LIMIT 10 OFFSET 20 skips 20 rows and returns the next 10. Or shorthand: LIMIT 20, 10 (offset first, then count). Large OFFSET values are slow (MySQL scans and discards rows). For efficient deep pagination, use keyset/cursor-based pagination instead.
13. What is a JOIN?
JOIN combines rows from two or more tables based on a related column. INNER JOIN: rows with matching values in both tables. LEFT JOIN: all rows from left table, matched rows from right (NULLs where no match). RIGHT JOIN: reverse. FULL OUTER JOIN: not native in MySQL (use UNION of LEFT + RIGHT JOINs). CROSS JOIN: Cartesian product (every combination of rows).
14. What is an index in MySQL?
An index is a data structure (B-Tree by default in InnoDB) that speeds up data retrieval. Without an index, MySQL scans every row (full table scan). With an index, it jumps to matching rows directly. Creates overhead on INSERT/UPDATE/DELETE (index must be maintained). Index frequently searched, joined, or sorted columns. Too many indexes slow down write operations.
15. What is NULL in MySQL?
NULL represents unknown or missing value - it's not zero or empty string. NULL != NULL (comparing NULL to anything yields NULL, not true/false). Use IS NULL or IS NOT NULL to check. In aggregate functions: COUNT(*) counts all rows, COUNT(col) skips NULLs. NULL values sort last in ASC order. COALESCE(col, "default") returns a fallback when the column is NULL.
16. Difference between DELETE and TRUNCATE?
DELETE: removes specific rows matching WHERE, logged in the transaction log, fires triggers, can be rolled back, allows WHERE filter. TRUNCATE: removes all rows instantly (no WHERE), minimal logging, resets AUTO_INCREMENT, can't be rolled back in most cases (DDL in MySQL, though InnoDB makes it transactional). TRUNCATE is much faster for emptying a table; DELETE for selective removal.
17. What does AUTO_INCREMENT do?
AUTO_INCREMENT automatically generates a unique incrementing integer value for a column (usually the primary key) on each INSERT without specifying a value. MySQL tracks the next value to use. Gaps can occur (from failed inserts, rollbacks). The counter never decreases. Use BIGINT AUTO_INCREMENT for tables that will grow large to avoid integer overflow at 2 billion rows.
18. What is a default value in MySQL?
DEFAULT sets a value used when no value is provided for a column during INSERT. Examples: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, status VARCHAR(20) DEFAULT "active", score INT DEFAULT 0. If you INSERT without specifying the column, the default is used. Defaults can be constant values or a limited set of MySQL functions (CURRENT_TIMESTAMP, NOW(), etc.).
19. What is the purpose of GROUP BY?
GROUP BY groups rows with the same value in specified columns into summary rows. Used with aggregate functions (COUNT, SUM, AVG, MAX, MIN). SELECT department, COUNT(*) FROM employees GROUP BY department. Each row in the result represents one group. Columns in SELECT must be either in GROUP BY or inside an aggregate function. HAVING filters groups after aggregation.
20. What is an aggregate function?
Aggregate functions compute a single value from multiple rows. COUNT(*): total row count. COUNT(col): non-NULL values count. SUM(col): total of values. AVG(col): average. MAX(col)/MIN(col): largest/smallest value. GROUP_CONCAT(col): combines values into a string. Used with GROUP BY to compute per-group summaries. NULL values are ignored by all aggregates except COUNT(*).
21. How does MySQL’s InnoDB storage engine manage transactions internally?
InnoDB transactions: each transaction writes changes to the redo log (on disk) before data pages are modified (write-ahead logging). Uncommitted data pages modified in the buffer pool. On COMMIT: redo log flushed to disk, transaction committed. On ROLLBACK: undo log reverses changes. MVCC allows concurrent reads of consistent snapshots without blocking writers or readers.
22. What is MVCC (Multi-Version Concurrency Control) in MySQL?
MySQL InnoDB MVCC: each row has two hidden columns - DB_TRX_ID (transaction that last modified it) and DB_ROLL_PTR (pointer to undo log for old versions). When a transaction reads a row, it uses a read view (snapshot of active transactions at read start) to determine which row version is visible. Older versions are stored in the undo log and accessed via the rollback pointer chain.
23. What is the purpose of the InnoDB buffer pool?
InnoDB buffer pool is the main memory cache for data and index pages. MySQL reads pages from disk into the buffer pool and serves subsequent reads from memory. Sized by innodb_buffer_pool_size - set to 70-80% of available RAM for dedicated DB servers. The bigger it is, the more data fits in memory, fewer disk reads. Monitor with SHOW ENGINE INNODB STATUS for buffer pool hit rate.
24. What is a covering index?
Covering index: the index contains all columns needed to satisfy a query without accessing the actual data rows. MySQL can fulfill the query entirely from the index B-Tree. Seen in EXPLAIN as "Using index" (no "Using where" or table access). Example: index on (user_id, email) covers SELECT email FROM users WHERE user_id = 1. Dramatically faster than regular index access for frequently-run queries.
25. What is an execution plan and why is EXPLAIN important?
EXPLAIN shows the query execution plan without running the query. Key columns: type (ALL=full scan, index, range, ref, const - const is best), key (index used), rows (estimated rows examined), Extra (Using where, Using index, Using filesort, Using temporary). Using filesort and Using temporary signal potential performance issues. Always run EXPLAIN on slow queries before adding indexes.
26. What is the difference between row-level and table-level locking?
Table-level locking: the entire table is locked for one operation. Simple but allows no concurrency on the same table (MyISAM). Row-level locking: only the specific rows being accessed are locked. Other rows (and thus other transactions) are unaffected (InnoDB). Row-level locking enables much higher concurrency on write-heavy workloads. InnoDB uses row-level locking with next-key locks for range queries.
27. How does MySQL handle indexing on large tables?
Large table indexing considerations: index creation is slow (reads all rows) and blocks writes by default (MySQL 5.6+ has Online DDL for InnoDB). Indexes consume disk space and slow down writes. Use ONLINE algorithm for adding indexes: ALTER TABLE ... ADD INDEX ... ALGORITHM=INPLACE, LOCK=NONE. Analyze query patterns before adding indexes. More isn't better - each index has a write cost.
28. What is a slow query log and when should you enable it?
Slow query log records queries that exceed long_query_time seconds. Enable: slow_query_log=ON, slow_query_log_file=/path/to/log, long_query_time=1.0. Also log queries without indexes: log_queries_not_using_indexes=ON. Use mysqldumpslow or pt-query-digest to analyze the log and find top offenders. Enable in production to catch performance regressions early.
29. What is the difference between logical and physical backups?
Logical backup: exports data as SQL statements (INSERT) or CSV - readable, portable, slow to restore. Tools: mysqldump, mysqlpump. Physical backup: copies raw data files - fast to restore, same MySQL version required. Tools: MySQL Enterprise Backup, Percona XtraBackup (hot backup - no downtime). Use logical for small databases and portability, physical for large databases where restore speed matters.
30. What is a stored routine and why use it?
Stored routine (procedure or function) encapsulates SQL logic in the database. Benefits: network efficiency (multiple SQL statements executed in one call), reusability across applications, security (grant EXECUTE without table access), consistency (business rules enforced at DB level). Downsides: difficult to version control, unit test, and debug. Modern practice favors application-layer logic with ORMs.
31. How does MySQL handle foreign key constraints during deletes or updates?
InnoDB foreign key on DELETE: CASCADE (delete child rows too), SET NULL (set FK to NULL), RESTRICT/NO ACTION (block delete if children exist - default). On UPDATE: CASCADE (update FK in children), SET NULL, RESTRICT. MySQL checks FK constraints on every DELETE and UPDATE of parent rows. Add an index on the FK column in the child table - MySQL requires it for performance.
32. What is partitioning and when is it useful?
Partitioning splits one logical table into multiple physical segments (partitions) based on a key. Types: RANGE (by date range), LIST (by specific values), HASH (evenly distributed), KEY (like hash using MySQL's hashing). Benefits: partition pruning (queries only scan relevant partitions), faster DELETE of old data (DROP PARTITION), parallel queries across partitions. Best for time-series data with range queries on the partition key.
33. What are FULLTEXT indexes and when are they used?
FULLTEXT indexes enable natural language text search. Supported on TEXT, VARCHAR, CHAR columns in InnoDB (MySQL 5.6+). Create: CREATE FULLTEXT INDEX idx ON articles(title, body). Query: WHERE MATCH(title, body) AGAINST("search terms" IN BOOLEAN MODE). Much faster than LIKE "%term%" for text search. Supports relevance ranking. Not suitable for partial word matching or complex linguistic analysis.
34. What is a deadlock detector in InnoDB?
InnoDB's deadlock detector checks for deadlock cycles every second. When detected, it rolls back the transaction with the least amount of undo (typically the most recently started). The rolled-back transaction receives error 1213 (Deadlock found). The application must catch this error and retry the transaction. You can view the last deadlock with SHOW ENGINE INNODB STATUS.
35. How do prepared statements improve performance and security?
Prepared statements: parse and plan the SQL once, execute many times with different parameters. Security: parameters are sent separately from the query (prevents SQL injection - no string interpolation). Performance: plan is cached in the session. In application code: most drivers use prepared statements by default when you pass parameters. At DB level: PREPARE stmt FROM "SELECT * FROM users WHERE id = ?"; EXECUTE stmt USING @id.
36. What are triggers and their performance implications?
Triggers add overhead: they execute hidden SQL for every matching DML operation, increasing write latency. Cascading triggers (trigger fires another trigger) multiply the overhead. Triggers are not visible in EXPLAIN, making performance debugging harder. Alternatives: application-layer logic or scheduled jobs for audit/denormalization. Only use triggers for database-level enforcement that can't be enforced at the application layer.
37. What is the difference between CHAR, VARCHAR, and TEXT in performance?
CHAR(n): fixed-length, padded with spaces. Fast for fixed-size values (codes, flags). VARCHAR(n): variable-length with 1-2 byte length prefix. Best for most text columns. TEXT/MEDIUMTEXT/LONGTEXT: stored outside the row when large, slower to access. For short text (<255 chars): VARCHAR. For long documents: TEXT. CHAR is slightly faster for fixed-size data due to no length calculation. VARCHAR is almost always the right choice.
38. How do you optimize JOIN performance in MySQL?
Optimize MySQL JOINs: ensure join columns are indexed on both sides (especially the child table FK). Use EXPLAIN to check join type (ref is good, ALL is bad). Smaller table should drive the join (MySQL's optimizer usually handles this). Use covering indexes that include join and filter columns. Avoid functions on join columns (breaks index usage). Rewrite complex JOINs with derived tables or CTEs if needed.
39. How does MySQL handle replication?
MySQL replication: primary (master) writes changes to the binary log (binlog). Replica (slave) has an I/O thread that copies binlog to its relay log, and a SQL thread that replays the relay log. Replication is asynchronous by default (replica may lag). Supports: read scaling (route SELECTs to replicas), high availability (promote replica if primary fails), and backups (take backup from replica). GTID replication makes failover easier.
40. What are common reasons for slow MySQL performance?
Common MySQL performance issues: missing indexes (causes full table scans), bad indexes (too many or low cardinality), N+1 queries from ORMs, large result sets fetched but only a few rows used (add LIMIT), SELECT * fetching unused columns, queries with functions on indexed columns (index not used), buffer pool too small (constant disk I/O), and connection overhead (use connection pooling).
41. What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN: returns only rows where there is a match in BOTH tables - unmatched rows from either table are excluded. LEFT JOIN (LEFT OUTER JOIN): returns ALL rows from the left table, matched rows from the right table; unmatched right-side columns are NULL. Use LEFT JOIN when the right-side data is optional (e.g., users who may or may not have orders).
42. What is a composite primary key?
Composite primary key is a primary key made up of two or more columns whose combined values must be unique. Example: PRIMARY KEY (order_id, product_id) for an order_items table. Neither column alone needs to be unique - only the combination. Used for many-to-many junction tables or when no single natural column uniquely identifies a row. InnoDB stores rows in composite PK order.
43. What is the use of the DISTINCT keyword?
DISTINCT removes duplicate rows from query results. SELECT DISTINCT country FROM users returns each country only once even if multiple users share the same country. Applied after the query runs - MySQL evaluates all rows then removes duplicates. Can be slow on large result sets. Use GROUP BY as an alternative when also performing aggregation. Cannot DISTINCT on a subset of selected columns.
44. What is an alias in MySQL?
Aliases give a temporary name to a column or table in a query. Column alias: SELECT first_name AS name. Table alias: FROM employees e JOIN departments d ON e.dept_id = d.id. Aliases make queries shorter and more readable. Column aliases can be used in ORDER BY but not in WHERE (evaluated before aliases are applied). Table aliases are required when joining a table to itself (self join).
45. What is the purpose of HAVING clause?
HAVING filters groups after GROUP BY aggregation - WHERE can't reference aggregate values. SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department HAVING avg_sal > 50000. WHERE runs before grouping (filters individual rows), HAVING runs after grouping (filters groups). Use WHERE to filter rows, HAVING to filter aggregated results.
46. What is the difference between UNION and UNION ALL?
UNION combines results from two SELECT statements and removes duplicates (deduplication step). UNION ALL combines results and keeps all rows including duplicates (faster - no dedup). Both queries must have the same number of columns with compatible types. Use UNION ALL unless you specifically need duplicate removal - it's always faster since MySQL skips the sort/dedup step.
47. What is a subquery?
A subquery is a SELECT statement nested inside another SQL statement. Can appear in WHERE (filter with subquery result), FROM (inline view/derived table), SELECT (scalar subquery). Correlated subquery references the outer query's columns and executes once per outer row (can be slow). Non-correlated subquery executes once, result reused. Often replaceable with JOINs for better performance.
48. What is a self join?
A self join joins a table to itself. Requires table aliases to distinguish the two instances. Common use: hierarchical data (employee and their manager in the same table). SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id. The self join treats the same table as two logical tables with different roles.
49. What are MySQL data types?
MySQL data types: Numeric (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL), String (CHAR, VARCHAR, TEXT, BLOB, ENUM, SET), Date/Time (DATE, TIME, DATETIME, TIMESTAMP, YEAR), Binary (BINARY, VARBINARY, BLOB types), Spatial types (POINT, GEOMETRY), JSON (native JSON type in MySQL 5.7+). Choose the smallest type that fits your data.
50. What is a view?
A view is a saved SELECT query presented as a virtual table. Doesn't store data itself - queries the underlying tables each time accessed. Benefits: simplify complex queries (hide JOINs), security (expose only certain columns), abstraction (insulate apps from schema changes). Updatable views allow INSERT/UPDATE/DELETE if they meet certain conditions (no GROUP BY, DISTINCT, UNION, etc.).