50 MySQL Interview Questions (With Answers)

MySQL interviews test your understanding of InnoDB internals, indexing strategies, query optimisation, transaction isolation, replication, and administration. This guide covers 50 of the most common questions with concise answers and SQL examples.

Quick reference

Architecture

1. What is the difference between InnoDB and MyISAM?

InnoDB is almost always the right choice. MyISAM is legacy; avoid it for any table that needs concurrency or reliability.

2. How does InnoDB's buffer pool work?

The buffer pool is InnoDB's in-memory cache for data and index pages. It uses a modified LRU (Least Recently Used) algorithm:

• The pool is divided into two sub-lists: young (recently accessed) and old (candidates for eviction).
• New pages enter at the midpoint (the boundary between young and old) to protect the young list from one-time full-table scans.
• Configure its size with innodb_buffer_pool_size — a common rule is 70–80 % of available RAM for a dedicated DB server.

-- Check buffer pool hit rate (should be > 99%)
SELECT
  (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
    AS buffer_pool_hit_rate
FROM (
  SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_reads
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r,
(
  SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;

3. What is the InnoDB redo log and undo log?

Both work together: redo ensures durability, undo enables atomicity and consistent reads.

4. What is a clustered index in InnoDB?

In InnoDB, every table has exactly one clustered index — the primary key. The actual row data is stored in the leaf nodes of this B-tree:

• Rows are physically ordered by primary key value.
• Secondary indexes store the primary key value as the row reference.

Implications:

• Choose a small, monotonically increasing PK (e.g. INT AUTO_INCREMENT) to avoid page splits on insert.
• Avoid UUIDs as PKs — random insertion causes heavy page fragmentation.
• Secondary index lookups require two B-tree traversals: secondary index → PK → clustered index.

5. What is the difference between innodb_flush_log_at_trx_commit values?

For ACID compliance in production use 1. Value 2 is acceptable if replication provides an additional safety net.

Data types

6. When would you use CHAR vs VARCHAR?

Use CHAR for truly fixed-width values (country codes, ISO codes, MD5 hashes). Use VARCHAR for everything else.

7. What is the difference between INT and BIGINT? When does it matter?

Use INT UNSIGNED AUTO_INCREMENT for most PKs (up to ~4 billion rows). Switch to BIGINT when you approach 2 billion rows, because AUTO_INCREMENT wraps around on signed INT.

8. What are the DATE, DATETIME, and TIMESTAMP differences?

• TIMESTAMP auto-converts to UTC on storage and back to the session timezone on retrieval — great for tracking events across timezones.
• DATETIME stores exactly what you insert — good for birth dates or appointment times that should not change with timezone.
• TIMESTAMP has the year-2038 problem; prefer DATETIME for long-lived data.

9. When should you use ENUM?

-- ENUM definition
CREATE TABLE orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  status ENUM('pending', 'processing', 'shipped', 'cancelled') NOT NULL DEFAULT 'pending'
);

Pros: compact storage (1–2 bytes), readable values, validated on insert.

Cons: adding a new value requires ALTER TABLE (locks the table before MySQL 5.6 online DDL). Avoid ENUM if the list of values changes frequently — use a foreign-keyed lookup table instead.

10. How does MySQL store JSON data?

MySQL 5.7+ has a native JSON data type that validates JSON on insert and stores it in a binary format:

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  attributes JSON
);

INSERT INTO products (attributes)
VALUES ('{"color": "red", "size": "M", "tags": ["sale", "new"]}');

-- Extracting values
SELECT JSON_EXTRACT(attributes, '$.color')    AS color,
       attributes->>'$.size'                   AS size,
       JSON_OVERLAPS(attributes->'$.tags', '["sale"]') AS on_sale
FROM products;

Use JSON for truly schema-less attributes. For structured data with known columns, normalised columns outperform JSON in indexing and query speed.

Indexes

11. How does a MySQL B-tree index work?

InnoDB uses a B+ tree for all standard indexes:

• Internal nodes contain separator keys.
• Leaf nodes contain the actual key values (and, for secondary indexes, the corresponding primary key).
• All leaf nodes are linked in a doubly-linked list, enabling efficient range scans.
• Height is typically 3–4 levels for tables with millions of rows, so most lookups cost 3–4 I/Os.

-- Creating an index
CREATE INDEX idx_last_name ON users(last_name);

-- Index-aware query
SELECT * FROM users WHERE last_name = 'Smith'; -- uses idx_last_name

12. What is a composite index and what is the leftmost prefix rule?

A composite index covers multiple columns:

CREATE INDEX idx_name_city ON users(last_name, city);

MySQL can use this index for:

• WHERE last_name = 'Smith' ✓
• WHERE last_name = 'Smith' AND city = 'London' ✓
• WHERE city = 'London' ✗ (city is not the leftmost column)

The optimizer can use a prefix of the index — it must start from the leftmost column. Put high-cardinality, equality-filtered columns first.

13. What is a covering index?

A covering index contains all columns the query needs, so MySQL never needs to read the table (the clustered index) — the secondary index scan is sufficient.

CREATE INDEX idx_covering ON orders(customer_id, created_at, total);

-- This query is covered: only reads the index
SELECT customer_id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 10;

EXPLAIN will show Using index in the Extra column — a sign the query is using a covering index.

14. What is an index prefix and when would you use one?

For long VARCHAR or TEXT columns, you can index only the first N characters:

CREATE INDEX idx_email_prefix ON users(email(20));

When to use: indexing full TEXT/BLOB columns is not allowed; prefix indexes reduce index size for long strings.

Caveat: prefix indexes cannot be covering indexes and may cause false-positive row fetches for distinctness checks.

15. What is the difference between a unique index and a primary key?

Both enforce uniqueness. The PK doubles as the clustered index, so it physically determines row ordering.

16. How do full-text indexes work in MySQL?

CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  body TEXT,
  FULLTEXT KEY ft_title_body (title, body)
);

-- Natural language search
SELECT *, MATCH(title, body) AGAINST ('mysql performance' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('mysql performance' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

-- Boolean mode with operators
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST ('+mysql -oracle' IN BOOLEAN MODE);

Full-text indexes use an inverted index structure. Available in InnoDB since MySQL 5.6. For advanced use cases consider Elasticsearch.

Queries

17. What does EXPLAIN show and how do you read it?

EXPLAIN SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.country = 'DE'
GROUP BY u.id;

Key columns to check:

Use EXPLAIN ANALYZE (MySQL 8.0+) to get actual execution statistics.

18. What is the N+1 query problem and how do you fix it?

-- N+1 problem: 1 query for users + N queries for their orders
SELECT * FROM users;       -- 100 rows
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
-- ... 98 more queries

-- Fix: single JOIN
SELECT u.id, u.name, o.id AS order_id, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

In ORMs (Laravel Eloquent, Hibernate): use eager loading (with('orders')) to issue a single IN (...) query instead of N individual queries.

19. What is the difference between HAVING and WHERE?

-- WHERE filters rows before aggregation
SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE active = 1              -- excludes inactive rows first
GROUP BY department
HAVING AVG(salary) > 70000;  -- then filters groups

You cannot reference aggregate functions in WHERE — use HAVING for that.

20. Explain the different types of JOINs in MySQL.

-- INNER JOIN: only matching rows
SELECT u.name, o.id FROM users u INNER JOIN orders o ON o.user_id = u.id;

-- LEFT JOIN: all users, NULL if no order
SELECT u.name, o.id FROM users u LEFT JOIN orders o ON o.user_id = u.id;

-- RIGHT JOIN: all orders (rarely needed; swap tables and use LEFT JOIN instead)
SELECT u.name, o.id FROM users u RIGHT JOIN orders o ON o.user_id = u.id;

-- CROSS JOIN: Cartesian product
SELECT a.val, b.val FROM table_a a CROSS JOIN table_b b;

-- Self JOIN
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

MySQL has no native FULL OUTER JOIN — emulate it with LEFT JOIN UNION ALL RIGHT JOIN WHERE left_key IS NULL.

21. What are window functions and how do you use them?

-- ROW_NUMBER: unique rank per partition
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- LAG: compare current row with previous
SELECT
  date,
  revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

-- Running total
SELECT date, revenue,
       SUM(revenue) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS cumulative
FROM daily_sales;

Available in MySQL 8.0+. They run after WHERE/GROUP BY/HAVING but before ORDER BY/LIMIT.

22. How do you find duplicate rows and remove them?

-- Find duplicates
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING cnt > 1;

-- Delete duplicates, keeping the row with the lowest id
DELETE u1
FROM users u1
INNER JOIN users u2
  ON u1.email = u2.email AND u1.id > u2.id;

23. What is a subquery and when should you prefer a JOIN?

-- Correlated subquery (runs once per outer row — often slow)
SELECT name FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000
);

-- Equivalent JOIN (often faster)
SELECT DISTINCT u.name
FROM users u
INNER JOIN orders o ON o.user_id = u.id AND o.total > 1000;

Prefer JOINs in most cases — the optimizer handles them better. Use subqueries when they improve readability (especially with EXISTS / NOT EXISTS or derived tables).

Transactions & locking

24. What are the four MySQL transaction isolation levels?

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

InnoDB's REPEATABLE READ is the default. It prevents phantoms for the same query via MVCC snapshot — but gap locks prevent INSERT phantoms for SELECT ... FOR UPDATE.

25. What is a deadlock and how does InnoDB handle it?

A deadlock occurs when two transactions each hold a lock the other needs:

TXN A: locks row 1, waits for row 2
TXN B: locks row 2, waits for row 1

InnoDB automatically detects deadlocks and rolls back the smaller transaction (by undo log size), returning ERROR 1213 (40001): Deadlock found.

Prevention strategies:

• Always acquire locks in the same order across transactions.
• Keep transactions short.
• Use SELECT ... FOR UPDATE only when necessary.
• Check SHOW ENGINE INNODB STATUS\G for the latest deadlock details.

26. What is the difference between optimistic and pessimistic locking?

-- Optimistic locking with version column
UPDATE inventory
SET quantity = quantity - 1, version = version + 1
WHERE id = 5 AND version = 3;
-- If 0 rows affected, someone else changed it; retry

Use pessimistic for high-contention scenarios; optimistic for mostly-read workloads.

27. What is MVCC in MySQL?

Multi-Version Concurrency Control allows InnoDB to give each transaction a consistent snapshot of the data at the start of the transaction (in REPEATABLE READ):

• Each row has hidden columns: DB_TRX_ID (last-modifying transaction) and DB_ROLL_PTR (pointer to undo log).
• When a transaction reads a row, InnoDB checks if the row's transaction ID is visible in the current snapshot; if not, it reconstructs the older version from the undo log.
• Result: readers never block writers and writers never block readers.

28. What is SELECT ... FOR UPDATE and when do you use it?

START TRANSACTION;
-- Lock the row exclusively; other sessions block on SELECT FOR UPDATE / SELECT FOR SHARE
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Use FOR UPDATE when you need to read and then modify a row atomically (e.g. balance transfers, inventory decrement). Without it, a race condition between the SELECT and UPDATE is possible.

Performance

29. How do you identify slow queries?

-- Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;   -- queries > 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Or query performance_schema
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e12 AS avg_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

The slow query log and pt-query-digest (Percona Toolkit) are standard tools for finding problematic queries in production.

30. What causes a query to use a full table scan?

MySQL avoids indexes and does a full scan when:

• No index exists on the filtered column.
• The optimizer estimates the scan is cheaper (e.g. fetching > 30% of rows).
• A function is applied to the indexed column: WHERE YEAR(created_at) = 2024 — prevents index use; rewrite as WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'.
• Implicit type conversion: WHERE user_id = '42' when user_id is INT — may prevent index use in edge cases.
• LIKE '%keyword%' (leading wildcard prevents B-tree traversal).

31. What is the query cache and why was it removed?

MySQL had a query cache (until 5.7, removed in 8.0) that stored the complete result of a SELECT. Problems:

• A single WRITE on the table invalidated all cached results for that table.
• The global mutex was a severe bottleneck under high concurrency.
• Setting query_cache_size = 0 (default in 5.7) was the common advice.

Modern alternative: use application-level caching (Redis/Memcached) or ProxySQL query caching.

32. How do you optimize a query with ORDER BY and LIMIT?

-- Offset pagination is slow for deep pages (must scan all previous rows)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 100000, 20;

-- Keyset (cursor) pagination is O(log n)
SELECT * FROM posts
WHERE created_at < '2024-06-01 10:00:00'   -- last seen value
ORDER BY created_at DESC
LIMIT 20;

For ORDER BY to use an index, the ORDER BY columns must be a leftmost prefix of an index (in the same order and direction as the index definition, with no extra filtered columns between them).

33. What is ANALYZE TABLE and OPTIMIZE TABLE?

Run ANALYZE TABLE after bulk inserts/deletes or when EXPLAIN shows obviously wrong row estimates.

Replication

34. How does MySQL binary log replication work?

1. The primary records every data-modifying event in the binary log (binlog).
2. The replica's IO thread connects to the primary and copies the binlog to the replica's relay log.
3. The replica's SQL thread reads the relay log and applies the events.

-- On primary: check binlog position
SHOW MASTER STATUS\G

-- On replica: check replication status
SHOW REPLICA STATUS\G

Replication formats:

35. What is GTID replication?

Global Transaction Identifiers (GTIDs) assign a unique ID to every committed transaction: server_uuid:sequence_number.

Benefits:

• Failover is simple — replicas automatically find the correct binlog position.
• Circular replication protection built-in.
• Easy to verify all transactions have been replicated.

-- Enable GTID
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

36. What is the difference between synchronous and asynchronous replication?

MySQL Group Replication (and Galera Cluster) provide synchronous multi-primary replication with automatic failover.

Schema design

37. What is database normalisation and what are the normal forms?

Normalise to 3NF as a baseline. Denormalise intentionally for read-heavy reporting tables where join cost outweighs storage cost.

38. When would you use partitioning?

MySQL table partitioning splits a single logical table into multiple physical segments:

CREATE TABLE sales (
  id BIGINT AUTO_INCREMENT,
  sale_date DATE NOT NULL,
  amount DECIMAL(10,2),
  PRIMARY KEY (id, sale_date)   -- PK must include partition key
)
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

Benefits: partition pruning means queries that filter on the partition key scan only relevant partitions. Also enables fast partition drops (vs slow DELETEs) for time-series data.

Caveat: partitioning has limited benefit if queries don't filter on the partition key.

39. How do foreign keys work in MySQL?

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
);

Foreign keys enforce referential integrity but add overhead on every write. Some teams disable them and enforce integrity at the application layer for maximum insert throughput.

40. What is the AUTO_INCREMENT gotcha on rollbacks?

InnoDB's AUTO_INCREMENT counter is not rolled back with a transaction:

START TRANSACTION;
INSERT INTO users (name) VALUES ('Alice');  -- gets id = 100
ROLLBACK;

INSERT INTO users (name) VALUES ('Bob');   -- gets id = 101, not 100

This is by design — reverting AUTO_INCREMENT could cause gaps or duplicate assignment in concurrent scenarios. Never rely on AUTO_INCREMENT values being contiguous.

Stored routines

41. What is the difference between a stored procedure and a stored function?

-- Stored procedure
DELIMITER $$
CREATE PROCEDURE get_user_orders(IN p_user_id INT)
BEGIN
  SELECT o.id, o.total, o.created_at
  FROM orders o
  WHERE o.user_id = p_user_id
  ORDER BY o.created_at DESC;
END$$
DELIMITER ;

CALL get_user_orders(42);

-- Stored function
DELIMITER $$
CREATE FUNCTION full_name(first VARCHAR(100), last VARCHAR(100))
RETURNS VARCHAR(201)
DETERMINISTIC
BEGIN
  RETURN CONCAT(first, ' ', last);
END$$
DELIMITER ;

SELECT full_name(first_name, last_name) FROM users;

42. What are triggers and when should you avoid them?

DELIMITER $$
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE users SET order_count = order_count + 1 WHERE id = NEW.user_id;
END$$
DELIMITER ;

When to avoid:

• Triggers are invisible to the application layer — makes debugging hard.
• They run inside the transaction; slow trigger logic delays commits.
• Cascading triggers (trigger calling trigger) are notoriously hard to reason about.
• Bulk INSERT/UPDATE operations fire triggers once per row — severe performance hit.

Prefer explicit application logic or background jobs where possible.

43. What are MySQL events?

MySQL events are scheduled tasks managed by the event scheduler:

SET GLOBAL event_scheduler = ON;

CREATE EVENT purge_old_logs
ON SCHEDULE EVERY 1 DAY
STARTS '2025-01-01 03:00:00'
DO
  DELETE FROM audit_logs WHERE created_at < NOW() - INTERVAL 90 DAY;

Useful for routine maintenance tasks. For complex scheduling needs prefer external schedulers (cron, Kubernetes CronJob) for better visibility and error handling.

Security & administration

44. How do you manage users and privileges in MySQL?

-- Create user
CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'strong_password';

-- Grant selective privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'10.0.0.%';

-- Create read-only user
GRANT SELECT ON mydb.* TO 'reporter'@'localhost';

-- Revoke
REVOKE DELETE ON mydb.* FROM 'app'@'10.0.0.%';

-- See grants
SHOW GRANTS FOR 'app'@'10.0.0.%';

-- Remove user
DROP USER 'app'@'10.0.0.%';

FLUSH PRIVILEGES;  -- needed only if manually editing the grant tables

Principle of least privilege: never run application code as root or with global GRANT OPTION.

45. How do you back up MySQL databases?

# InnoDB consistent backup without locking
mysqldump --single-transaction --routines --triggers \
  --databases mydb > backup_$(date +%F).sql

# Compress
mysqldump --single-transaction mydb | gzip > backup_$(date +%F).sql.gz

# Restore
gunzip < backup_2025-01-01.sql.gz | mysql mydb

46. How do you prevent SQL injection in MySQL?

Always use parameterised queries / prepared statements — never concatenate user input into SQL:

# Python — safe
cursor.execute("SELECT * FROM users WHERE email = %s", (user_input,))

# PHP — safe with PDO
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => $user_input]);

# Node.js — safe with mysql2
connection.execute("SELECT * FROM users WHERE email = ?", [userInput]);

Additional layers: validate/sanitise input, use least-privilege DB users, enable sql_mode = STRICT_TRANS_TABLES.

47. What is SHOW ENGINE INNODB STATUS useful for?

SHOW ENGINE INNODB STATUS\G

Key sections to check:

• TRANSACTIONS: active transactions and lock waits.
• LATEST DETECTED DEADLOCK: full deadlock details — which queries, which rows, which rolled back.
• BUFFER POOL AND MEMORY: buffer pool hit rate, dirty pages.
• ROW OPERATIONS: inserts/updates/deletes per second.
• FILE I/O: pending reads/writes.

Useful for diagnosing lock contention, deadlocks, and memory pressure.

Advanced topics

48. What is MySQL Group Replication vs InnoDB Cluster?

InnoDB Cluster is the recommended HA setup for MySQL 8.0+.

49. What is the EXPLAIN ANALYZE output in MySQL 8.0?

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id
ORDER BY COUNT(o.id) DESC
LIMIT 10\G

Unlike EXPLAIN, EXPLAIN ANALYZE actually executes the query and shows:

• Estimated vs actual rows.
• Estimated vs actual cost.
• Time spent in each node.
• Loop counts for nested operations.

This reveals cases where the optimizer's cardinality estimates are wrong and helps pinpoint the expensive step.

50. What are some MySQL-specific performance anti-patterns?

MySQL vs PostgreSQL vs MariaDB

Common mistakes

FAQ

Q: Is MySQL or PostgreSQL better? For most web applications, both are excellent choices. PostgreSQL has stronger standards compliance, richer data types (arrays, JSONB, range types), better support for complex queries, and a more permissive license. MySQL has a larger market share, slightly simpler initial setup, and deep integration with web stacks (LAMP). Choose PostgreSQL for new projects where query complexity or data type richness matters; MySQL/Aurora is a strong choice for high-throughput read-heavy workloads with a large ecosystem.

Q: What is the difference between MySQL and MariaDB? MariaDB is a community fork of MySQL created in 2009 after Oracle acquired MySQL. It is API/SQL-compatible and is the default MySQL replacement in many Linux distributions. MariaDB adds features like Galera Cluster, Spider engine, and more storage engines. However, as of version 10.x, MariaDB and MySQL have diverged significantly internally, so they are no longer drop-in compatible in all scenarios.

Q: How do I check the size of each table in MySQL?

SELECT
  table_schema AS `database`,
  table_name,
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS `size_MB`
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY (data_length + index_length) DESC;

Q: What is the difference between TRUNCATE and DELETE? TRUNCATE TABLE t removes all rows instantly by dropping and recreating the table structure. It is not transactional (cannot be rolled back in MySQL), resets AUTO_INCREMENT, and does not fire triggers. DELETE FROM t removes rows one-by-one, fires triggers per row, can be filtered with WHERE, and is transactional (can be rolled back). Use TRUNCATE to empty large tables quickly; DELETE when you need row-level control or triggers.

Q: How does Aurora MySQL differ from standard MySQL? Amazon Aurora MySQL is compatible with MySQL 5.7/8.0 but uses a distributed, log-structured storage layer (replicated across 6 copies in 3 AZs). Key differences: storage auto-scales to 128 TB, read replicas share the same storage (no replication lag for reads), failover is faster (~30 seconds), and writes go to a shared redo log — not to InnoDB's traditional data files. Aurora is not open source; it is AWS-managed only.

Q: How do I migrate from MySQL 5.7 to MySQL 8.0?

1. Back up with mysqldump --single-transaction.
2. Run mysqlcheck --all-databases --check-upgrade on the 5.7 instance.
3. Check deprecated features: utf8mb3 → utf8mb4, GROUP BY no longer implicitly sorts, sql_mode changes, ONLY_FULL_GROUP_BY is now default.
4. Upgrade in-place or restore the dump into a new 8.0 instance.
5. Run mysql_upgrade (automatic in 8.0.16+ on first start).
6. Test application queries — watch for ONLY_FULL_GROUP_BY errors and removed functions.