Interview Questions
MySQL Interview Questions and Answers
MySQL-specific questions go beyond generic SQL to cover storage engines, indexing strategy, and transaction isolation -- the details that separate "can write a query" from "can design a schema that performs well at scale."
Example: Using EXPLAIN to check if a query uses an index
SQLEXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- Look at the "type" and "key" columns in the output:
-- type = ALL -> full table scan, no index used (slow on a large table)
-- type = ref -> an index was used to look up matching rows (fast)
-- key = NULL -> confirms no index was actually used
-- If customer_id has no index, add one:
CREATE INDEX idx_orders_customer_id ON orders (customer_id);Frequently Asked Questions
InnoDB (the default in modern MySQL) supports transactions, foreign keys, and row-level locking. MyISAM is older, doesn't support transactions or foreign keys, and locks at the table level, which causes far more contention under concurrent writes. Almost all new tables should use InnoDB; MyISAM only really comes up maintaining legacy schemas today.
In InnoDB, the primary key is a clustered index -- the actual row data is physically stored in primary key order, so looking up by primary key is very fast. Every other index (a non-clustered/secondary index) stores the indexed column's value plus a pointer back to the primary key, requiring an extra lookup for the full row.
On a column with very low cardinality (e.g. a boolean "is_active" flag on a table where 95% of rows are true) -- the index doesn't narrow the search enough to be worth using, and MySQL's query planner may ignore it in favor of a full scan. Indexes also add write overhead (every INSERT/UPDATE must maintain them), so indexing every column isn't free.
READ UNCOMMITTED (can see other transactions' uncommitted changes -- "dirty reads"), READ COMMITTED (only sees committed data, but a value can change between two reads in the same transaction), REPEATABLE READ (MySQL's InnoDB default -- consistent reads within a transaction), and SERIALIZABLE (strictest, effectively runs transactions as if sequential, at a real performance cost).
INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table, with NULLs filled in for columns from the right table where no match exists -- useful when you want, say, every customer including ones with zero orders.
A common pattern: GROUP BY the columns that define a duplicate, use HAVING COUNT(*) > 1 to find the duplicated groups, then DELETE keeping only the row with the minimum (or maximum) id in each group -- typically via a subquery or a temporary table, since MySQL doesn't allow deleting from a table you're simultaneously selecting from in the same statement without one.
CHAR(n) is fixed-length -- MySQL always stores/reads exactly n characters, padding shorter values with spaces. VARCHAR(n) is variable-length, storing only the actual characters plus a small length prefix. CHAR can be marginally faster for fixed-length data (like a 2-letter country code); VARCHAR is the right default for most text.