Database · Fresher-relevant
MySQL interview questions on DDL/DML, transactions, stored procedures, indexing strategies, and ACID properties.
Commonly asked at: Commonly asked at product companies and IT services firms such as TCS, Infosys, and Amazon (for backend/SDE roles) — general pattern, not company-verified.
Company names below are mentioned only to indicate the general type/level of interview these questions are common at, based on widely known industry patterns. This content is not affiliated with, endorsed by, or sourced from any confidential material of the named companies. All trademarks belong to their respective owners.
What is the difference between CHAR and VARCHAR?
Tip: Lead with the storage behavior, then give a one-line "when to use which."
CHAR is fixed-length — MySQL always reserves the declared length and pads shorter values with spaces. VARCHAR is variable-length — it only stores the actual characters plus a small length prefix. I'd use CHAR for something always the same size, like a 2-letter state code, and VARCHAR for anything that varies, like a name or email.
What is a primary key vs a unique key?
Tip: Mention the NULL-handling difference — it's the detail interviewers listen for.
A primary key uniquely identifies each row, can't be NULL, and a table can only have one. A unique key also enforces uniqueness but allows one NULL value per column, and you can have several unique keys on the same table.
INNER JOIN vs LEFT JOIN — what's the difference?
Tip: Give a concrete one-line example — interviewers want to see you can apply it, not just recite it.
INNER JOIN only returns rows with a match in both tables. LEFT JOIN returns everything from the left table, and matching data from the right — if there's no match, the right side comes back as NULL. I'd use LEFT JOIN when I want to keep all records from one table regardless of whether a related record exists, like listing all customers even if they haven't placed an order yet.
What is normalization, and why do we use it?
Tip: Give one concrete redundancy example — it makes an abstract answer concrete.
Normalization organizes tables to cut down redundancy and avoid update anomalies, usually by splitting data across related tables. For example, instead of repeating a customer's address on every order row, you store it once in a customers table and just reference it by ID. The tradeoff is more JOINs at query time.
What is an index, and how does it help performance?
Tip: Always mention the write-side cost — it shows you understand tradeoffs, not just benefits.
An index is a structure — usually a B-tree in InnoDB — that lets the database find rows fast without scanning the whole table. It speeds up SELECTs with WHERE, JOIN, or ORDER BY on indexed columns, but every INSERT/UPDATE/DELETE now also has to update the index, so over-indexing a write-heavy table can actually hurt performance.
WHERE vs HAVING — what's the difference?
Tip: State clearly that HAVING is for aggregates — that's the one-line answer interviewers are checking for.
WHERE filters rows before any grouping and can't use aggregate functions like COUNT or SUM. HAVING filters after GROUP BY and can reference those aggregates. So I'd use WHERE to exclude inactive users first, then HAVING to keep only departments with more than 5 people after grouping.
What are transactions, and what does ACID mean?
Tip: Just define each letter briefly — don't over-explain, this is a quick recall question.
A transaction is a group of statements executed as one unit — either all succeed or none do. ACID stands for Atomicity, Consistency, Isolation, and Durability, and InnoDB supports it fully, unlike the older MyISAM engine.
DELETE vs TRUNCATE vs DROP — what's the difference?
Tip: Rank them by "how much they remove" — rows, all rows, or the whole table — that's the cleanest way to explain it.
DELETE removes rows one at a time, can be filtered and rolled back. TRUNCATE removes all rows at once, is faster, resets auto-increment, but usually can't be rolled back. DROP removes the entire table structure — after that, the table doesn't exist anymore.
What is the difference between a clustered and a non-clustered index?
Tip: Mention that InnoDB only allows one clustered index per table — that's a detail that shows real depth.
A clustered index determines the physical order data is stored in on disk — there can only be one per table, and in InnoDB it's usually the primary key. A non-clustered index is a separate structure that points back to the actual row, so a table can have many of them.
⭐ Scenario: A query that used to run fast has suddenly become slow in production. How would you approach fixing it?
Tip: This is scenario-based — walk through your process, don't just list theory.
Situation: A previously fast query started timing out after the data grew significantly.
Task: I needed to find out why it slowed down and fix it without breaking existing functionality.
Action: I ran `EXPLAIN` on the query to check whether it was still using an index or had started doing a full table scan — often caused by data growth changing the query planner's choice, or a missing index on a newly-filtered column. I also checked if the table had been recently altered or if statistics were stale.
Result: I found the query was doing a full scan on a column that didn't have an index anymore relative to the new data volume; adding the right composite index brought the query time back down.
InterviewEra generates role-specific questions using your actual projects and skills. Get scored feedback on technical depth, clarity, and structure — free to start.