MySQL is the most popular open-source database for web applications. These MySQL interview questions cover data types, keys and basic queries for freshers, then storage engines, indexing, transactions and isolation levels, replication, backups and performance tuning for experienced developers and DBAs.
30 questions · Updated
Fundamentals, definitions and simple scenarios. Good for freshers and warm-ups.
MySQL is an open-source relational database management system owned by Oracle.
It stores data in tables and is queried with SQL.
It is used by WordPress, Laravel and countless web applications, and by large companies such as Facebook and Uber at scale.
MariaDB is a popular fork.
CHAR has a fixed length and pads shorter values with spaces, which suits values of constant length such as country codes.
VARCHAR has variable length and stores only the characters used plus one or two bytes for the length, so it saves space for values like names and emails.
TEXT is for long content.
AUTO_INCREMENT automatically generates a unique, increasing number for a column, usually the primary key, when a new row is inserted.
LAST_INSERT_ID() returns the value generated in the current session.
Gaps can appear after rollbacks or deletes, so values should not be relied on to be continuous.
Numeric types (TINYINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE), strings (CHAR, VARCHAR, TEXT, BLOB, ENUM, SET), dates and times (DATE, TIME, DATETIME, TIMESTAMP, YEAR), JSON, and spatial types. Choosing the smallest suitable type saves storage and memory and makes indexes faster, for example TINYINT for a status flag rather than INT.
FLOAT and DOUBLE store approximate binary values, so 0.1 + 0.2 may not equal exactly 0.3, and rounding errors add up in financial calculations.
DECIMAL(10,2) stores exact decimal values with fixed precision, so amounts such as prices, balances and taxes are always correct.
FLOAT suits scientific measurements where tiny errors do not matter.
DATETIME stores a date and time exactly as given, with a range from year 1000 to 9999, and no time zone conversion.
TIMESTAMP stores values in UTC and converts them to and from the session time zone, with a range from 1970 to 2038.
TIMESTAMP suits event times across time zones; DATETIME suits dates such as birthdays or appointments that should not shift.
In MySQL, the old utf8 (utf8mb3) charset stores at most three bytes per character, so it cannot store emojis and some rare characters. utf8mb4 is real UTF-8 with four bytes, supporting every character, and is the default in MySQL 8 with the utf8mb4_0900_ai_ci collation. Using it everywhere avoids broken text and errors when users enter emojis.
CREATE USER 'app'@'%' IDENTIFIED BY 'strong-password'; then GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'%';.
Applications should get only the privileges they need on their own database, never root.
MySQL 8 supports roles to group privileges, SHOW GRANTS shows a user's rights, and REVOKE removes them.
DESCRIBE table or SHOW COLUMNS FROM table lists columns and types, SHOW CREATE TABLE table shows the full definition including indexes, keys, engine and charset, and SHOW INDEX FROM table lists indexes with their cardinality. information_schema tables give the same data for queries across many tables.
No. MySQL supports INNER, LEFT, RIGHT and CROSS joins, but not FULL OUTER JOIN. You can emulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION, which removes duplicate matched rows, or by using a LEFT JOIN plus a RIGHT JOIN filtered to unmatched rows with UNION ALL for better performance.
GROUP_CONCAT joins values from a group into one string, such as listing all product names per order: SELECT order_id, GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') FROM order_items GROUP BY order_id.
The result is cut off at group_concat_max_len (1024 bytes by default), which may need increasing.
JSON_ARRAYAGG is an alternative that returns a JSON array.
Applied problems, trade-offs and questions about your own projects.
InnoDB is the default engine: it supports transactions, row-level locking, foreign keys and crash recovery, which suits almost all applications.
MyISAM uses table-level locking, has no transactions or foreign keys and can lose data on a crash.
MyISAM is now considered legacy.
READ UNCOMMITTED allows dirty reads, READ COMMITTED sees only committed data, REPEATABLE READ (the InnoDB default) gives a consistent snapshot for the whole transaction, and SERIALIZABLE makes transactions behave as if run one after another. Higher levels prevent more anomalies (dirty reads, non-repeatable reads and phantoms) but increase locking and contention.
In InnoDB, table data is stored in the order of the primary key, which is the clustered index.
Secondary indexes store the primary key value, so a lookup by a secondary index finds the primary key and then reads the row.
That is why a short primary key is better, and why a covering index that contains all needed columns avoids the second lookup.
I enable the slow query log with a long_query_time threshold, analyse it with pt-query-digest or mysqldumpslow, and check performance_schema and sys schema views for the most expensive statements. For a specific query I run EXPLAIN or EXPLAIN ANALYZE to see whether it uses indexes, how many rows it scans and whether it uses temporary tables or filesort.
Key columns are type (from best to worst: const, eq_ref, ref, range, index, ALL, where ALL means a full table scan), possible_keys and key (which index was chosen), rows (estimated rows examined), filtered, and Extra, where "Using index" means a covering index, and "Using filesort" or "Using temporary" signal extra work. EXPLAIN ANALYZE runs the query and shows actual times.
A composite index on (a, b, c) can be used for queries filtering on a, on a and b, or on a, b and c, but not on b or c alone, because the index is sorted by a first.
A range condition on a column stops later columns from being used for further filtering.
So the column order should match common queries, typically equality columns first and range columns last.
A covering index contains every column a query needs, so MySQL can answer it from the index alone without reading the table rows, which EXPLAIN shows as "Using index".
For example, an index on (customer_id, created_at, total) covers SELECT created_at, total FROM orders WHERE customer_id = 42.
It greatly speeds up frequent queries, at the cost of larger indexes.
The JSON type stores validated JSON documents, queried with JSON_EXTRACT or the -> and ->> operators.
JSON values cannot be indexed directly, so you create a generated column that extracts a value, such as a city from an address, and index it, or use a functional index in MySQL 8.
Generated columns can also compute values such as a full name or a total.
INSERT ... ON DUPLICATE KEY UPDATE inserts a row, or updates it if a primary key or unique key already exists, such as INSERT INTO stock (sku, qty) VALUES ('A1', 5) ON DUPLICATE KEY UPDATE qty = qty + 5. INSERT IGNORE skips duplicates silently, and REPLACE deletes and reinserts the row, which can trigger cascading deletes, so it is used carefully.
InnoDB uses row-level locks: shared locks for reads with FOR SHARE and exclusive locks for writes and SELECT ... FOR UPDATE.
In Repeatable Read, it also uses gap locks and next-key locks on index ranges to prevent phantom rows, which can surprise developers by blocking inserts.
Statements that cannot use an index may lock many rows, so good indexes reduce lock contention.
Views simplify complex queries for reporting and restrict which columns users can see.
Stored procedures and functions keep logic in the database, which can reduce round trips for batch operations, and events schedule recurring jobs.
Most teams keep business logic in the application for easier testing and deployment, and use procedures only where they clearly help.
System design, deep internals, leadership and tough follow-ups.
The source server writes changes to its binary log.
Replicas read the binary log through an I/O thread into a relay log and apply the changes with SQL threads.
Replication can be asynchronous (default) or semi-synchronous, and GTIDs make failover easier.
Replicas are used for read scaling, backups and high availability; Group Replication and InnoDB Cluster provide automatic failover.
A deadlock happens when two transactions each hold a lock the other needs.
InnoDB detects it and rolls back one transaction with an error.
I prevent deadlocks by accessing tables and rows in a consistent order, keeping transactions short, using proper indexes so fewer rows are locked, and retrying the failed transaction in the application.
SHOW ENGINE INNODB STATUS shows the latest deadlock.
Logical backups with mysqldump or mysqlpump export SQL statements, which is simple and portable but slow for large databases; --single-transaction gives a consistent InnoDB snapshot without locking.
Physical backups with Percona XtraBackup or MySQL Enterprise Backup copy data files and are much faster.
Combined with binary logs, they allow point-in-time recovery.
Backups must be tested by restoring them regularly.
Window functions and common table expressions (including recursive), roles, a transactional data dictionary, invisible indexes for safely testing index removal, descending indexes, functional indexes, JSON improvements, utf8mb4 as default, instant ADD COLUMN, better optimiser histograms, and the stronger caching_sha2_password authentication. MySQL now also has LTS and innovation release tracks.
innodb_buffer_pool_size (often 60 to 75% of RAM on a dedicated server) so data and indexes stay in memory, innodb_log_file_size or innodb_redo_log_capacity for write-heavy loads, innodb_flush_log_at_trx_commit for the durability versus speed trade-off, max_connections combined with connection pooling, and tmp_table_size. Query and index design usually matter more than settings.
Partitioning splits one table into parts stored separately, by RANGE (such as by year), LIST, HASH or KEY.
Queries that filter on the partition key only read relevant partitions, and dropping an old partition deletes old data instantly.
Every unique key must include the partition column, foreign keys are not supported, and it does not help queries that cannot be pruned.
Use replication with automatic failover.
InnoDB Cluster combines Group Replication (synchronous, multi-node replication with automatic primary election), MySQL Router and MySQL Shell.
Alternatives are asynchronous or semi-synchronous replicas with Orchestrator, ProxySQL for routing and read/write splitting, or managed services such as Amazon RDS Multi-AZ and Aurora.
MySQL 8 can add columns instantly (ALGORITHM=INSTANT) and perform many changes in place with LOCK=NONE.
For changes that would copy and lock a huge table, use tools such as gh-ost or pt-online-schema-change, which copy data to a new table in the background, keep it in sync and swap it in.
Changes should be backward compatible so old and new application versions both work.
See all Database Engineer interview questions, covering SQL, MySQL, PostgreSQL, Oracle, SQL Server and more.