MySQL Interview Questions

• INNER JOIN - returns rows that match in both tables.
• LEFT JOIN - all rows of the left table plus matches from the right; NULLs where there is no match.
• RIGHT JOIN - all rows of the right table plus matches from the left; the mirror of LEFT JOIN.

LEFT JOIN is by far the most common in practice; RIGHT JOIN can almost always be rewritten as a LEFT JOIN.

• Atomicity - all-or-nothing; a failed transaction leaves no partial writes.
• Consistency - the database moves from one valid state to another, preserving constraints.
• Isolation - concurrent transactions do not see each other's uncommitted changes.
• Durability - committed data survives crashes.

InnoDB enforces ACID with undo logs, redo logs and locks.

An index is a sorted structure (B+tree) that lets MySQL jump straight to the matching rows instead of scanning the whole table. The cost: every write (INSERT/UPDATE/DELETE) must also maintain the index, and indexes consume disk space.

Choose indexes for columns used in WHERE, JOIN and ORDER BY. Avoid indexing low-selectivity columns and avoid too many or overlapping indexes. Use EXPLAIN to verify the query plan.

• READ UNCOMMITTED - may read uncommitted changes (dirty reads).
• READ COMMITTED - reads only committed data; non-repeatable reads possible.
• REPEATABLE READ (MySQL default) - the same SELECT returns the same result within a transaction; phantom reads possible.
• SERIALIZABLE - full serialisation; phantom reads prevented, lowest concurrency.

Higher isolation means stronger consistency but more locking and less concurrency.

InnoDB is the default and strongly recommended engine: supports transactions, foreign keys, row-level locking and crash recovery. Reads/writes scale under concurrency.

MyISAM is the older engine: table-level locking, full-text search legacy, no transactions or FKs. It is rarely the right choice today except for a few specialised read-only workloads.

1. Run EXPLAIN SELECT ... and check that indexes are used (look at the type and key columns).
2. Verify missing or unused indexes on the WHERE/JOIN columns.
3. Check for functions around indexed columns that defeat index use.
4. Avoid SELECT * - fetch only needed columns.
5. Review data volume: consider partitioning or archiving old rows.

Always test changes on a copy of the data before applying to production.

In MySQL, database and schema are the same thing - a namespace containing database objects. A table stores rows of structured data inside a database, with defined columns and types. Statements like CREATE DATABASE and CREATE SCHEMA are interchangeable.

Two prominent engines: InnoDB (default) is transactional and supports foreign keys, row-level locking, the buffer pool and crash recovery. MyISAM is non-transactional with table-level locking and full-text search, but no FK support. Others include MEMORY, ARCHIVE, CSV and the pluggable engine architecture. Use SHOW ENGINES to list them.

CHAR(n) has a fixed length - the column always occupies n characters, padded with spaces on the right (and trimmed on retrieval). VARCHAR(n) stores only the actual bytes plus a length prefix, up to n characters. Use CHAR for fixed-size data (country codes, hash values) and VARCHAR otherwise; VARCHAR usually saves space.

• INNER JOIN - only matching rows from both tables.
• LEFT JOIN - all rows of the left table plus matches; NULLs where no match.
• RIGHT JOIN - all rows of the right table plus matches.
• FULL OUTER JOIN - everything from both sides (not directly supported by MySQL; emulate with UNION).
• CROSS JOIN - cartesian product.

UNION combines the result sets of two queries and removes duplicates (extra sorting/comparison cost). UNION ALL keeps all rows, including duplicates, and is much faster. Use UNION when duplicate removal matters and UNION ALL when it does not or you already know the sets are disjoint.

A primary key uniquely identifies each row: it is NOT NULL and there is only one per table (it becomes the clustered index in InnoDB). A UNIQUE key also prevents duplicates but can be NULL (multiple NULLs allowed), and a table can have many unique keys.

A foreign key is a column that references the primary key (or unique key) of another table and enforces referential integrity. Actions define what happens when the referenced row changes: RESTRICT/NO ACTION (block), CASCADE (delete/update children), SET NULL (null out) and SET DEFAULT. InnoDB enforces this; MyISAM ignores it.

An index accelerates lookups, sorts and joins by organizing data in a searchable structure (B-tree). Types: normal/KEY (non-unique), UNIQUE, FULLTEXT (text search), SPATIAL (geometric), and covering indexes (contain all columns a query needs). Indexes speed reads but slow writes and use space.

Use a composite index on multiple columns queried together: INDEX (last_name, first_name). Column order matters - the index is used for leftmost prefixes. A query filtering only first_name cannot use this index, but one filtering last_name or both can. Order columns by the query access pattern (equality first, then range).

WHERE filters individual rows before grouping or aggregation and can never reference aggregate functions. HAVING filters grouped results after aggregation and can reference aggregates like COUNT(*). Example: SELECT dept, COUNT(*) FROM emp GROUP BY dept HAVING COUNT(*)>5. Both can appear in one query: WHERE runs first, then GROUP BY, then HAVING.

"Using where" in EXPLAIN means the storage engine returns candidate rows (via an index or a full scan) and MySQL must apply an additional WHERE filter on them before returning results. It is an execution detail, not a SQL construct - it simply tells you the query still filters rows after index lookup, which is usually fine but worth checking if you expect a pure index-only access path.

Group by the columns that should be unique and count:

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

This lists every email that appears more than once, showing the row counts per duplicate group.

• DELETE - removes rows, can have a WHERE clause, is logged row by row and fires triggers; inside a transaction it can be rolled back.
• TRUNCATE - removes all rows efficiently by dropping and recreating the tablespace; no WHERE, resets auto-increment in MySQL, cannot be rolled back easily.
• DROP - removes the whole table (or database) including its structure, indexes and permissions; the table is gone permanently.

Atomicity - all statements in a transaction succeed or all are rolled back together. Consistency - a transaction moves the database from one valid state to another, obeying constraints. Isolation - concurrent transactions do not interfere in unexpected ways; controlled by isolation levels. Durability - once committed, changes survive crashes (redo logs). InnoDB is fully ACID.

READ UNCOMMITTED - sees uncommitted changes (dirty reads). READ COMMITTED - only committed data; each statement sees a fresh snapshot (non-repeatable reads possible). REPEATABLE READ (InnoDB default) - a consistent snapshot for the whole transaction. SERIALIZABLE - transactions are fully isolated by locking. MySQL uses MVCC plus locking to provide these.

Dirty read - reading a row that another transaction updated but has not committed yet. Non-repeatable read - within one transaction, the same row reads differently because another transaction committed a change between the reads. Phantom read - a query returns a different set of rows because another transaction inserted/deleted rows matching the predicate between executions.

A deadlock occurs when two transactions each hold a lock the other needs, so neither can finish. InnoDB detects cycles automatically and rolls back the victim transaction (the one that made the least progress), returning error 1213 (ER_LOCK_DEADLOCK). Best practices: lock tables in a consistent order, keep transactions short and retry deadlocked transactions in application code.

Implicit (autocommit) transactions are the default in MySQL: each statement commits immediately. Explicit transactions are started with START TRANSACTION/BEGIN and ended with COMMIT or ROLLBACK; multiple statements share one atomic unit. Use explicit transactions whenever updates must be all-or-nothing.

InnoDB assigns the next auto-increment value to a row on insert; the value is stored per table and never reused. Gaps appear when inserts are rolled back (the value is consumed), rows are deleted, or InnoDB reserves ranges at startup. Gaps are by design and should not matter - never rely on contiguous ids.

DISTINCT removes duplicate rows from a result set. GROUP BY groups rows sharing column values so you can run aggregates per group (COUNT, SUM, AVG). ORDER BY sorts the final result rows. GROUP BY often implies DISTINCT-like behavior for the grouped columns, but its purpose is aggregation.

A subquery is a SELECT nested inside another query. Types: scalar (returns one value), row, column (returns one column, used with IN), and table (used in the FROM clause as derived table). It can be correlated (references the outer query) or non-correlated. Sometimes a subquery can be rewritten as a JOIN, which often performs better.

A non-correlated subquery is evaluated once and is independent of the outer query. A correlated subquery references columns from the outer query and is therefore re-executed for every outer row - it can be slow. Correlated examples: WHERE price > (SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category).

Steps: run EXPLAIN and look for 'Using filesort', 'Using temporary', full table scans and the type column; check that WHERE/ORDER BY/JOIN columns have indexes (including covering composite indexes); avoid functions or wrapping columns in conditions that defeat indexes; avoid SELECT * and selecting unused columns; keep statistics up to date; and consider partitions or denormalization only after basics. Also review the query cache and server buffer sizes.

Both mean the query can be answered using only an index and never touches the table data (the EXPLAIN "Using index" note). A covering index is any index that includes all columns the query needs; an index-only scan is the execution method that takes advantage of it. They are essentially the same result - least I/O possible.