MySQL Interview Questions and Answers

— ny_wk

MySQL interview questions usually test three things at once: whether you can write correct SQL, whether you understand how the engine stores and locks data, and whether you can administer a server safely in production. This guide organizes the most common questions into clear topics with corrected, accurate answers you can actually use, instead of a raw question dump.

Every answer below reflects current MySQL behavior (MySQL 8.0 and the 8.4 LTS line). Where the classic interview “cheat sheets” are wrong or outdated, the mistake is flagged and fixed so you don’t repeat it in front of an interviewer.

MySQL Fundamentals: What Interviewers Expect First

What is MySQL? MySQL is an open-source relational database management system (RDBMS) that stores data in tables and is queried using SQL (Structured Query Language). It is a multi-threaded, multi-user, client/server system known for speed, reliability, and broad platform support. It is now owned and developed by Oracle, distributed under the GPL, with commercial licenses available for embedding in closed-source products.

What is the difference between SQL and MySQL? SQL is the standardized language for querying relational data. MySQL is a specific product (a database server) that implements SQL plus its own extensions. A common follow-up: MariaDB is a community fork of MySQL created after the Oracle acquisition; it is largely compatible but has diverged in features.

What are DDL, DML, DCL, and TCL? SQL commands fall into four groups:

• DDL (Data Definition Language) defines schema: CREATE, ALTER, DROP, TRUNCATE.
• DML (Data Manipulation Language) changes data: SELECT, INSERT, UPDATE, DELETE.
• DCL (Data Control Language) manages permissions: GRANT, REVOKE.
• TCL (Transaction Control Language) manages transactions: COMMIT, ROLLBACK, SAVEPOINT.

Note: in MySQL, DDL statements are implicitly committed — you cannot roll back a CREATE TABLE the way you can roll back an INSERT.

Core SQL Query MySQL Interview Questions

This is where most candidates win or lose the interview. Be precise about syntax and semantics.

How do you count rows and unique values? Use COUNT(*) for total rows and DISTINCT for unique values:

1. Total rows: SELECT COUNT(*) FROM users;
2. Unique values: SELECT DISTINCT first_name FROM users;
3. Count of unique values: SELECT COUNT(DISTINCT first_name) FROM users;

Prefer COUNT(*) over COUNT(column) when you want every row, because COUNT(column) ignores rows where that column is NULL.

How do you return 100 rows starting from the 25th? Use LIMIT with an offset. The modern, readable form is:

SELECT title FROM books LIMIT 100 OFFSET 25;

The older shorthand LIMIT 25, 100 means offset 25, then 100 rows — the first number is the offset, the second is the count. This trips people up constantly, so memorize the order.

How do you paginate and also get the total count? The legacy approach used SQL_CALC_FOUND_ROWS with FOUND_ROWS(). That feature is deprecated in MySQL 8.0 and should not be recommended in 2026. The correct modern pattern is two simple queries — one paginated page and one count — which the optimizer handles more efficiently:

1. SELECT page_title FROM web_pages LIMIT 10 OFFSET 0;
2. SELECT COUNT(*) FROM web_pages;

How do you select rows matching a set of values? Use IN:

SELECT team_name FROM teams WHERE games_won IN (2, 4, 6, 8);

How do you find rows where a column is NULL? Use IS NULL, not =. Comparing anything to NULL with = returns NULL (treated as false):

SELECT user_name FROM users WHERE phone_number IS NULL;

JOINs: The Most Misexplained MySQL Interview Topic

Interviewers love JOIN questions because so many candidates explain them incorrectly. Here is the accurate picture.

What is the difference between LEFT JOIN and INNER JOIN? An INNER JOIN returns only rows that match in both tables. A LEFT (OUTER) JOIN returns every row from the left table, plus matching right-table columns, and fills NULL where there is no match.

Correcting a popular cheat-sheet error: Many MySQL interview lists claim that

SELECT u.user_name, i.user_isp FROM users u LEFT JOIN isps i USING (user_id);

is equivalent to a query that uses WHERE users.user_id = isps.user_id. This is wrong. Moving the join predicate into a WHERE clause filters out the unmatched left rows, which silently turns a LEFT JOIN into an INNER JOIN. To preserve outer-join semantics, the condition must stay in the ON/USING clause. This distinction is a frequent gotcha question.

How do you find the last auto-increment value inserted? Use LAST_INSERT_ID(). It returns the first auto-generated ID from your most recent INSERT on the current connection — so concurrent users do not interfere with each other:

SELECT LAST_INSERT_ID();

Modifying Tables and Handling Constraints

How do you widen a column from VARCHAR(10) to VARCHAR(50)? Use MODIFY when keeping the same name (cleaner than the older CHANGE, which requires repeating the name):

ALTER TABLE questions MODIFY content VARCHAR(50);

How do you drop a column? ALTER TABLE answers DROP COLUMN user_id;

How do you change a table’s storage engine? ALTER TABLE questions ENGINE = InnoDB;

You get a foreign key constraint error on DELETE. What now? The error means child rows in another table still reference the row you are deleting. The correct approaches, in order of preference:

1. Delete the dependent (child) rows first, then the parent row.
2. Define the foreign key with ON DELETE CASCADE so child rows are removed automatically.
3. As a last resort during bulk maintenance only, set SET FOREIGN_KEY_CHECKS = 0;, run the delete, then restore SET FOREIGN_KEY_CHECKS = 1;. Avoid this on a live application — it can leave orphaned rows and corrupt referential integrity.

When would you use ORDER BY in a DELETE? When you are deleting a specific number of rows by sort order rather than by primary key, e.g. removing the single oldest record:

DELETE FROM questions ORDER BY created_at ASC LIMIT 1;

How do you list all indexes on a table? SHOW INDEX FROM questions;

String, Date, and Function MySQL Interview Questions

How do you concatenate strings? Use CONCAT() (the + operator does math in MySQL, not string joining): SELECT CONCAT(first_name, ' ', last_name) FROM users;

How do you extract part of a string? SELECT SUBSTRING(title, 1, 10) FROM questions; (positions are 1-based).

What is the difference between CHAR_LENGTH and LENGTH? CHAR_LENGTH() counts characters; LENGTH() counts bytes. For plain ASCII they match, but for multi-byte UTF-8 text they differ — a single emoji can be one character but four bytes.

What do % and _ mean in a LIKE pattern? % matches zero or more characters; _ matches exactly one character. Example: SHOW DATABASES LIKE 'tech%'; lists databases starting with “tech”.

How do you work with dates? Useful functions include:

• Extract month: SELECT MONTH(created_at) FROM questions;
• Format a date: SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM questions;
• Add an interval: SELECT DATE_ADD(published_at, INTERVAL 3 MINUTE) FROM questions;
• Convert between formats: UNIX_TIMESTAMP() and FROM_UNIXTIME() bridge Unix epoch integers and readable YYYY-MM-DD HH:MM:SS values.

What are ENUM and SET? An ENUM column restricts a value to one item from a fixed list; a SET column allows any combination from a list. Internally they are stored as compact integers. Use them cautiously — changing the allowed list requires an ALTER TABLE, so a lookup table is often more flexible.

Storage Engines: InnoDB vs MyISAM

This is a guaranteed topic. The short version: InnoDB is the default and correct choice for nearly all modern workloads.

Why choose InnoDB? Row-level locking for high concurrency, full ACID transactions, foreign key enforcement, and automatic crash recovery. MyISAM is now legacy; its historical advantages (simple per-table files, fast COUNT(*) on whole tables, compression via myisamchk) rarely outweigh the lack of transactions and crash safety. Treat MyISAM as a topic to understand for older systems, not to deploy new.

What is the difference between TRUNCATE and DELETE? DELETE is DML, removes rows one by one (can use a WHERE clause), and can be rolled back inside a transaction. TRUNCATE is DDL, instantly empties the whole table, resets the auto-increment counter, and is not transaction-rollback-safe.

Indexing and Performance

What is an index and why does it matter? An index is a sorted data structure (a B+ tree in InnoDB) that lets the engine find rows without scanning the entire table, dramatically speeding up WHERE, JOIN, and ORDER BY. The trade-off is slower writes and extra disk space, since each index must be updated on every change.

How do you analyze a slow query? Prefix it with EXPLAIN to see the execution plan — which indexes are used, the join order, and estimated rows examined:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Look for type: ALL (a full table scan) and a NULL in the key column as red flags that an index is missing.

What is the difference between a clustered and a secondary index? In InnoDB the primary key is the clustered index — the table rows are physically stored in primary-key order. Every secondary index stores the primary key as its pointer, so a well-chosen, compact primary key keeps all indexes smaller and faster.

Administration and Security MySQL Interview Questions

What is the default MySQL port? 3306.

How do you start and stop the server? On Linux with systemd: sudo systemctl start mysql and sudo systemctl stop mysql (the old /etc/init.d/mysql start works only on legacy init systems). On Windows: net start MySQL and net stop MySQL.

How do you back up a database? Use mysqldump: mysqldump -h host -u user -p mydb > dbdump.sql. For large production databases, mysqlpump or a physical backup tool such as Percona XtraBackup is faster and supports parallelism.

How do you change a user’s password? The modern, correct method is SQL, not the old mysqladmin password shortcut:

ALTER USER 'app'@'localhost' IDENTIFIED BY 'newStrongPassword';

Can you store connection settings in a file? Yes — an option file such as ~/.my.cnf. Set its permissions to 600 so other users cannot read your credentials. Better still, use mysql_config_editor to store credentials in an encrypted .mylogin.cnf login path.

What are good MySQL security practices?

• Every account has a strong password; remove anonymous (no-username) accounts.
• Avoid wildcard hosts like 'user'@'%' that allow login from anywhere; scope hosts tightly.
• Grant the least privilege needed — do not hand applications the root account.
• Run mysql_secure_installation on new servers to drop the test database and tighten defaults.
• Use TLS for connections crossing a network, and keep the server patched.

What does the --i-am-a-dummy (safe-updates) flag do? It makes the client refuse UPDATE and DELETE statements that lack a WHERE clause or a LIMIT, preventing accidental full-table wipes. The standard name today is --safe-updates.

Transactions and Concurrency

What does ACID mean? Atomicity, Consistency, Isolation, Durability — the guarantees that make a transaction reliable. InnoDB provides all four; MyISAM does not.

What are the transaction isolation levels? READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (InnoDB’s default), and SERIALIZABLE. Higher levels prevent more anomalies (dirty reads, non-repeatable reads, phantom reads) at the cost of concurrency.

How do you write a transaction?

1. START TRANSACTION;
2. Run your INSERT/UPDATE/DELETE statements.
3. COMMIT; to save, or ROLLBACK; to undo everything since the start.

Key Takeaways

• Know your JOIN semantics: putting a join condition in WHERE instead of ON silently converts a LEFT JOIN into an INNER JOIN — a classic interview trap.
• Default to InnoDB: it gives transactions, row-level locking, foreign keys, and crash recovery; MyISAM is legacy.
• Use modern syntax: prefer LIMIT … OFFSET, ALTER USER … IDENTIFIED BY, and two-query pagination over deprecated SQL_CALC_FOUND_ROWS.
• Indexes are the performance lever: read EXPLAIN output, watch for full table scans, and design a compact clustered primary key.
• Security is least privilege: strong passwords, no wildcard hosts, no anonymous users, and never run apps as root.

Frequently Asked Questions

What MySQL version should I study for interviews in 2026?

Study MySQL 8.0 and the 8.4 LTS line, since they are the current production standards. Mentioning 8.0 features such as window functions, CTEs (common table expressions), and the deprecation of SQL_CALC_FOUND_ROWS signals that your knowledge is up to date.

Is MySQL or PostgreSQL better to know for interviews?

Both are valuable. MySQL dominates web stacks (LAMP, WordPress, many SaaS apps), while PostgreSQL is favored for complex queries and analytics. For a MySQL-titled role, master InnoDB internals, indexing, and replication; the SQL fundamentals transfer to either system.

What is the difference between MySQL and MariaDB?

MariaDB is a community-driven fork of MySQL started after Oracle’s acquisition. It is highly compatible at the SQL level but has diverged in storage engines, optimizer features, and some functions. Know that they exist as separate products and are not always drop-in interchangeable.

How do I prepare for the hands-on SQL portion of a MySQL interview?

Practice writing JOINs, aggregations with GROUP BY and HAVING, subqueries, and window functions against a sample schema. Be ready to read an EXPLAIN plan and explain how you would add an index to fix a slow query — application of concepts matters more than memorized definitions.

For more developer and sysadmin tutorials, subscribe on YouTube @explorenystream.