MySQL

MySQL 8.4 as interviewers probe it: InnoDB's clustered key, collations, strict mode, ONLY_FULL_GROUP_BY, JSON, upserts, EXPLAIN, REPEATABLE READ and the locks behind deadlocks.

Start the course Practise this with real questions

In this course

InnoDB and the clustered primary keyIn InnoDB the table is the primary-key index. That one fact explains covering indexes, why wide or random keys hurt, and what happens to a table with no key.9 min
Data types, strict mode and implicit conversionMySQL converts between strings and numbers silently. Know when it does, what strict mode refuses, and how a quoted number can switch an index off.10 min
Character sets and collationsutf8mb4 versus utf8mb3, what utf8mb4_0900_ai_ci treats as equal, why a UNIQUE key rejects a word you never inserted, and the illegal-mix-of-collations error.9 min
NULL semantics and the null-safe operatorThree-valued logic in MySQL: the <=> operator, why NOT IN goes empty, how aggregates skip NULL, where NULLs sort, and why a UNIQUE key allows many of them.9 min
Dates, times and time zonesDATETIME versus TIMESTAMP, month arithmetic that clamps, fractional seconds that round, and the half-open range that is both correct and index-friendly.10 min
Joins, MySQL style: FULL OUTER emulation and multi-table writesMySQL has no FULL OUTER JOIN, lets UPDATE and DELETE join other tables, and refuses a subquery that reads the table being changed. Here is how to work with all three.9 min
ONLY_FULL_GROUP_BY, ROLLUP and GROUP_CONCATWhy MySQL rejects some GROUP BY queries and accepts others that look the same, how functional dependence decides it, and two MySQL aggregates with sharp edges.10 min
Window functions in MySQL 8Ranking, LAG and running totals without collapsing rows, the default frame that surprises people on ties, and why a window function cannot go in WHERE.10 min
CTEs and recursive CTEsNamed subqueries with WITH, and WITH RECURSIVE for trees, where MySQL fixes column types from the anchor row and stops runaway recursion at 1000 levels.10 min
JSON columnsMySQL's JSON type: -> versus ->>, typed comparisons, JSON_TABLE to turn arrays into rows, multi-valued indexes, and when a JSON column is a normalisation shortcut you will regret.11 min
Upserts: ON DUPLICATE KEY UPDATE, REPLACE and INSERT IGNOREThree ways MySQL handles an insert that hits a unique key, what each one really does to the row, and the affected-rows and auto-increment side effects interviewers ask about.10 min
Composite indexes, the leftmost prefix and EXPLAINHow MySQL uses a multi-column index: equality columns from the left, at most one range, ORDER BY for free, and covering. Read it all from EXPLAIN.12 min
Generated columns and functional indexesColumns MySQL computes for you, VIRTUAL or STORED, and how they (or a functional index) make a WHERE on an expression or a JSON path searchable.10 min
Query tuning: sargable predicates, keyset pagination and plan treesMeasure how many rows a query really reads, rewrite the predicates that defeat indexes, replace deep OFFSET with a keyset, and read EXPLAIN FORMAT=TREE.12 min
Transactions in InnoDB: REPEATABLE READ, implicit commits and gap locksInnoDB's default isolation level, snapshot versus locking reads, the statements that commit for you, why an error does not roll back a transaction, and the locks behind deadlocks.12 min
Stored procedures, handlers and deadlock retryProcedures with IN and OUT parameters, SIGNAL for your own errors, handlers that catch them, cursors for row-by-row work, the sql_mode a routine keeps, and a retry loop for error 1213.12 min