Mysql Interview Questions & Answers for 2026

An index is a data structure (typically a B-tree) that MySQL maintains alongside a table to allow fast lookups without scanning every row. Without an index a SELECT with a WHERE clause requires a full table scan — O(n). With an index MySQL can find matching rows in O(log n). Primary keys are always indexed. Add indexes on columns used in WHERE, JOIN ON, and ORDER BY clauses for frequently run queries. The downside: indexes slow down INSERT/UPDATE/DELETE because MySQL must update the index. Use EXPLAIN to see whether queries use indexes.

INNER JOIN returns only rows where the join condition is true in both tables — rows with no match in either table are excluded. LEFT JOIN returns all rows from the left table and matched rows from the right; unmatched right-side columns are NULL. RIGHT JOIN is the mirror — all rows from the right, matched from the left. In practice use LEFT JOIN when you want all records from one table regardless of whether a related record exists (e.g. all users with their orders, including users with no orders). RIGHT JOIN is rarely used; it can always be rewritten as a LEFT JOIN.

Atomicity: a transaction is all-or-nothing — if any part fails the entire transaction is rolled back. Consistency: a transaction brings the database from one valid state to another — all constraints and rules are enforced. Isolation: concurrent transactions execute as if they were sequential — one transaction does not see uncommitted changes from another (controlled by isolation levels). Durability: once a transaction is committed it is permanent even if the system crashes immediately after. InnoDB (MySQL default storage engine) is fully ACID-compliant. MyISAM is not.

READ UNCOMMITTED: a transaction sees uncommitted data from others (dirty reads possible — dangerous, rarely used). READ COMMITTED: only sees committed data but non-repeatable reads can occur (same query returns different results in the same transaction). REPEATABLE READ (MySQL InnoDB default): same query returns same result within a transaction; phantom reads possible. SERIALIZABLE: highest isolation, fully serial execution — prevents all anomalies but worst performance. InnoDB uses MVCC (Multi-Version Concurrency Control) so reads do not block writes even at REPEATABLE READ.

First run EXPLAIN or EXPLAIN ANALYZE to see the execution plan — look for full table scans (type: ALL), missing indexes, and row estimates. Add appropriate indexes on WHERE and JOIN columns. Avoid SELECT * — fetch only needed columns. Rewrite subqueries as JOINs where possible. Use LIMIT when you only need top N rows. Check for implicit type conversions that prevent index use (e.g. WHERE varchar_col = 123). Enable the slow query log to find queries taking over a threshold. Consider query caching, table partitioning for very large tables, and read replicas for read-heavy loads.

CHAR(n) stores exactly n characters regardless of input length, padding shorter strings with spaces — it uses fixed storage and is slightly faster for fixed-length data like country codes or hashes. VARCHAR(n) stores up to n characters using only the space needed plus 1-2 bytes for length metadata — it is storage-efficient for variable-length strings. Use CHAR for fixed-length values (MD5 hash = CHAR(32), country code = CHAR(2)). Use VARCHAR for variable-length strings like names and emails. In modern storage-rich environments the performance difference is minimal.

Normalisation organises a relational database to reduce data redundancy and improve integrity. 1NF (First Normal Form): each column contains atomic values (no arrays or lists), and each row is unique. 2NF: must be in 1NF and every non-key column depends on the whole primary key (no partial dependencies — applies to composite keys). 3NF: must be in 2NF and no non-key column depends on another non-key column (no transitive dependencies). In practice most production databases normalise to 3NF. Denormalisation (intentionally violating normal forms) is used for read-heavy reporting tables to avoid expensive JOINs.

DELETE removes specific rows based on a WHERE condition; it is a DML statement, logged per row, fires triggers, and can be rolled back within a transaction. Without WHERE it deletes all rows but is slow on large tables. TRUNCATE removes all rows at once, is faster (deallocates pages, not logged per row), resets AUTO_INCREMENT, cannot be filtered, and cannot be rolled back in MySQL. DROP removes the entire table including its structure, indexes, and data — cannot be rolled back. Use DELETE for conditional row removal, TRUNCATE to quickly empty a table, DROP to remove the table permanently.