Top 50 MySQL Interview Questions and Answers

MySQL is an open-source relational database management system (RDBMS) using SQL.

• Owned by Oracle Corporation, widely used in web applications (the "M" in the classic LAMP stack).
• Known for speed, reliability, and ease of use, with strong community and commercial editions.

MySQL supports pluggable storage engines, each with different trade-offs:

• InnoDB (default): supports transactions, foreign keys, row-level locking.
• MyISAM: faster for read-heavy workloads but no transaction support, table-level locking.
• Memory: stores data in RAM for very fast, temporary access.
• Archive: optimized for storing large amounts of rarely-accessed historical data.

They differ significantly in features and use cases:

• InnoDB: supports transactions (ACID), foreign keys, and row-level locking — better for concurrent writes.
• MyISAM: no transaction/foreign key support, uses table-level locking, but can be faster for read-heavy, write-light workloads.
• InnoDB is the modern default and recommended choice for almost all use cases.

InnoDB has been the default storage engine since MySQL 5.5.

• Chosen as default due to its support for transactions, crash recovery, and foreign key constraints — features essential for most production applications.

AUTO_INCREMENT automatically generates a unique, incrementing integer value for a column, typically the primary key.

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

• Each new row automatically gets the next available integer, so you don't need to specify it manually on insert.

Basic DDL syntax in MySQL:

CREATE DATABASE shop;
USE shop;

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

• USE switches the active database context for subsequent statements.

Both store string data but with different storage strategies:

• CHAR(n): fixed-length, padded with spaces to always occupy n characters.
• VARCHAR(n): variable-length, stores only the actual characters plus 1-2 bytes of length metadata.
• CHAR can be slightly faster for fixed-size fields (like country codes); VARCHAR saves space for variable-length text.

MySQL provides several temporal types:

• DATE: stores a date only (YYYY-MM-DD).
• TIME: stores time only (HH:MM:SS).
• DATETIME: stores date and time, unaffected by timezone.
• TIMESTAMP: stores date and time, but is timezone-aware and auto-converts to UTC internally.
• YEAR: stores just a year value.

Both return current date/time values, but with different precision:

• NOW(): returns the current date and time (YYYY-MM-DD HH:MM:SS).
• CURDATE(): returns only the current date (YYYY-MM-DD).

SELECT NOW(), CURDATE();

ENUM restricts a column to one value from a predefined list of allowed strings.

CREATE TABLE orders (
  status ENUM('pending', 'shipped', 'delivered') DEFAULT 'pending'
);

• Internally stored as an integer for efficiency, while displaying as the string.
• Useful for fixed sets of options, though adding new values later requires an ALTER TABLE.

MySQL supports standard SQL JOIN syntax:

SELECT orders.id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

• Supports INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN — note MySQL does not support FULL OUTER JOIN directly (it must be emulated with UNION).

LIMIT restricts the number of rows returned by a query.

SELECT * FROM products ORDER BY price DESC LIMIT 10;

• Commonly combined with ORDER BY for "top N" queries, or with OFFSET for pagination.

Used together for pagination:

• LIMIT n: caps the number of rows returned.
• OFFSET n: skips the first n rows before starting to return results.

SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20; -- page 3, 10 per page

The standard command-line tool is mysqldump:

mysqldump -u root -p shop > shop_backup.sql
mysql -u root -p shop < shop_backup.sql

• mysqldump produces a logical backup (SQL statements) that can be restored on any compatible MySQL version.
• For very large databases, physical backup tools like mysqlbackup or Percona XtraBackup are faster.

A View in MySQL is a stored, virtual table based on a SELECT query.

CREATE VIEW active_orders AS
SELECT * FROM orders WHERE status != 'cancelled';

• Simplifies repeated complex queries and can restrict which columns/rows users see.

A Trigger automatically runs in response to INSERT, UPDATE, or DELETE events on a table.

CREATE TRIGGER before_insert_orders
BEFORE INSERT ON orders
FOR EACH ROW
SET NEW.created_at = NOW();

• Useful for auditing, validation, or automatically maintaining derived columns.

A Stored Procedure is precompiled SQL logic stored in the database, callable by name.

DELIMITER //
CREATE PROCEDURE GetUser(IN uid INT)
BEGIN
  SELECT * FROM users WHERE id = uid;
END //
DELIMITER ;

CALL GetUser(5);

Replication copies data changes from one MySQL server to others in near real-time.

• Master-Slave (Source-Replica): one primary handles writes, replicas serve reads.
• Master-Master: multiple servers can accept writes, syncing changes to each other (more complex conflict handling).
• Group Replication: a newer multi-primary, fault-tolerant approach built into MySQL.

The query cache stored the result of a SELECT statement, returning it instantly for identical subsequent queries.

• Removed in MySQL 8.0 due to scalability issues — it caused contention under high write concurrency.
• Modern MySQL relies instead on efficient indexing, buffer pools, and application/external caching (e.g., Redis).

EXPLAIN shows the query execution plan MySQL intends to use.

EXPLAIN SELECT * FROM orders WHERE customer_id = 10;

• Reveals whether indexes are used, estimated rows scanned, and join order — essential for diagnosing slow queries.

Indexes speed up lookups on specific columns.

CREATE INDEX idx_orders_customer ON orders(customer_id);

• Most commonly implemented as B-tree structures internally.
• Adding too many indexes can slow down writes, since each index must also be updated.

A Full-Text Index enables efficient natural-language searching within text columns.

ALTER TABLE articles ADD FULLTEXT(content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('mysql tutorial');

• Supported on InnoDB and MyISAM tables — much faster than LIKE '%term%' for text search.

They differ in concurrency granularity:

• MyISAM (table-level locking): locks the entire table for writes, blocking all other writes until released — poor concurrency.
• InnoDB (row-level locking): locks only the specific rows being modified, allowing many concurrent writes to different rows — much better concurrency for multi-user applications.

Transactions group multiple statements into an atomic unit, supported by the InnoDB engine.

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

• ROLLBACK undoes all changes made since the transaction started, if something goes wrong.

Both end a transaction, but with opposite effects:

• COMMIT: permanently saves all changes made during the transaction.
• ROLLBACK: discards all changes made during the transaction, reverting to the state before it began.

A Foreign Key enforces that a column's values must exist in a referenced table's primary/unique key.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

• Only supported by transactional storage engines like InnoDB — MyISAM ignores foreign key definitions.

ON DELETE CASCADE automatically deletes dependent rows when the referenced row is deleted.

FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE

• Deleting a customer automatically deletes all their orders too, maintaining referential integrity without manual cleanup.
• Alternatives include ON DELETE SET NULL and ON DELETE RESTRICT (the default, which blocks the delete).

They control whether negative values are allowed:

• SIGNED (default): allows both negative and positive values.
• UNSIGNED: only allows non-negative values, but doubles the positive range for the same storage size.
• Useful for columns like IDs or counts that are never negative.

age INT UNSIGNED

Common optimization steps include:

• Run EXPLAIN to see if indexes are being used effectively.
• Add indexes on columns used in WHERE, JOIN, and ORDER BY.
• Avoid SELECT * — fetch only needed columns.
• Avoid functions on indexed columns in WHERE clauses (they prevent index usage).
• Consider query restructuring, denormalization, or caching for very expensive queries.

The slow query log records queries that take longer than a configured threshold (long_query_time) to execute.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

• Essential for identifying performance bottlenecks in production without manually profiling every query.

information_schema is a built-in metadata database providing information about all other databases, tables, columns, and permissions on the server.

SELECT * FROM information_schema.tables WHERE table_schema = 'shop';

• Useful for introspection, tooling, and writing scripts that need to inspect schema structure dynamically.

Both store date and time, but differ in range and timezone handling:

• DATETIME: stores exactly as given, no timezone conversion, range up to year 9999.
• TIMESTAMP: stored internally as UTC and converted to the session's timezone on retrieval, but limited range (up to 2038).
• TIMESTAMP columns can also auto-update on row modification with ON UPDATE CURRENT_TIMESTAMP.

User-defined variables (prefixed with @) let you store a value temporarily within a session.

SET @total = 0;
SELECT @total := @total + amount FROM transactions;

• Useful for running totals, ranking, or passing values between statements within a session.

They manage user permissions:

• GRANT: gives a user specific privileges on a database/table.
• REVOKE: removes previously granted privileges.

GRANT SELECT, INSERT ON shop.* TO 'app_user'@'%';
REVOKE INSERT ON shop.* FROM 'app_user'@'%';

The Event Scheduler runs SQL statements automatically on a defined schedule, similar to a cron job inside the database.

CREATE EVENT cleanup_old_logs
ON SCHEDULE EVERY 1 DAY
DO DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

• Must be enabled globally with SET GLOBAL event_scheduler = ON;.

Partitioning splits a large table into smaller physical pieces (partitions) based on a defined rule, while still appearing as a single logical table.

• Types include RANGE, LIST, HASH, and KEY partitioning.
• Improves query performance and maintenance (e.g., dropping old partitions) for very large tables, especially time-series data.

MySQL has no built-in Materialized View, unlike PostgreSQL/Oracle.

• A regular View recomputes its query every time it's accessed.
• To emulate a materialized view, developers manually create a real table and populate it periodically (e.g., via a scheduled event or trigger), trading storage for faster reads.

MySQL treats NULL specially in UNIQUE columns:

• Multiple NULL values are allowed in a UNIQUE column, since NULL is considered "unknown" rather than a duplicate value.
• Only non-null duplicate values violate the constraint.

Both can express the same join logically, but differ in clarity and safety:

-- old style
SELECT * FROM a, b WHERE a.id = b.a_id;
-- modern style
SELECT * FROM a INNER JOIN b ON a.id = b.a_id;

• The comma syntax is easy to accidentally turn into a CROSS JOIN if the WHERE condition is forgotten — explicit JOIN ... ON syntax is clearer and safer, and is the modern standard.

MySQL's InnoDB engine defaults to REPEATABLE READ.

• This differs from many other databases (like PostgreSQL and Oracle) which default to READ COMMITTED.
• InnoDB's REPEATABLE READ, combined with MVCC, also helps prevent phantom reads in most common cases.

A Deadlock occurs when two transactions each hold a lock the other is waiting for, creating a cycle.

• InnoDB automatically detects deadlocks and rolls back one of the transactions (usually the one with the smaller impact) to break the cycle, returning an error to that transaction.
• Applications should catch this error and retry the transaction.

SHOW PROCESSLIST displays all currently running threads/connections on the MySQL server.

SHOW PROCESSLIST;

• Useful for identifying long-running or stuck queries, and for diagnosing performance issues in real time.

Both are logical backup tools, but mysqlpump is the newer, more capable option:

• mysqldump: single-threaded, simple, widely compatible.
• mysqlpump: supports parallel dumping of multiple tables/databases for faster backups, and can dump user accounts/privileges directly.

Both provide multi-primary, synchronous clustering for MySQL, where any node can accept writes.

• Galera Cluster: a third-party (originally Codership) synchronous multi-master replication solution.
• Group Replication: Oracle's built-in equivalent, offering similar multi-primary fault tolerance natively in MySQL 5.7+.

Both enforce uniqueness, but handle NULL differently:

• Primary Key: implicitly NOT NULL — no NULL values allowed at all.
• Unique Key: allows multiple NULL values, since MySQL doesn't treat NULLs as duplicates of each other.

A Composite Index spans multiple columns.

CREATE INDEX idx_name_email ON users(last_name, first_name);

• Column order matters: the index is most useful for queries filtering on a leftmost prefix of the columns (e.g., last_name alone, or last_name + first_name, but not first_name alone).

sql_mode controls how strictly MySQL validates and interprets SQL syntax and data.

• Modes like STRICT_TRANS_TABLES reject invalid data instead of silently truncating/converting it.
• ONLY_FULL_GROUP_BY enforces standard SQL rules for GROUP BY queries.
• Configurable globally or per-session to match application expectations.

Both store string data, but with different limits and storage:

• VARCHAR(n): stored inline in the row (up to a max length you define, ~65,535 bytes shared across the row), can be indexed fully.
• TEXT: designed for larger text blobs, often stored off-page, and can only be indexed with a prefix length.
• Use VARCHAR for typical bounded text (names, emails); TEXT for long-form content like articles.

Basic LIMIT/OFFSET becomes slow on large offsets since MySQL must scan and discard skipped rows.

-- slow for large offsets
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 100000;

-- faster: keyset/cursor pagination
SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 20;

• Keyset pagination (using a WHERE id > last_seen_id) avoids scanning skipped rows entirely.

They differ in format and restore speed:

• Logical backups (e.g., mysqldump): export data as SQL statements — portable across versions/platforms, but slower to restore.
• Physical backups (e.g., Percona XtraBackup): copy the actual data files — much faster to restore, but less portable and version-sensitive.