Browse Oracle Certification Guides

MySQL 1Z0-909 Cheat Sheet

December 13, 2025

MySQL 1Z0-909 cheat sheet for key facts, traps, service mappings, and final review.

On this page

Use this for last-mile review. 1Z0-909 rewards precise SQL semantics, predictable row-count reasoning, and optimizer-aware schema choices.

1Z0-909 answer sequence

Use this when the stem mixes SQL design, indexing, storage, or DBA operational checks.

    flowchart TD
	  S["Scenario"] --> D["Classify the workload and access pattern"]
	  D --> Q["Check schema, query, or index fit"]
	  Q --> O["Check storage, config, or runtime evidence"]
	  O --> V["Verify with plan, status, or safe change"]

Read every SQL or schema stem in this order

  1. Decide whether the question is about result correctness, schema design, index use, transaction behavior, stored program behavior, or JSON access.
  2. Trace the row set before focusing on displayed columns.
  3. Check NULL, join shape, grouping grain, and predicate placement.
  4. Only then inspect index fit, optimizer behavior, or transaction scope.
  5. Prefer the simplest correct design that preserves clarity, performance, and operability.

Fast lane picker

If the question is mainly about… Start with… Usual winning move
wrong query result join shape, NULL, and filter placement row set first
slow query sargability, index fit, and EXPLAIN optimizer evidence first
concurrency effect transaction scope and locking keep transactions short and intentional
JSON access extraction versus indexed generated column query path before storage preference
stored-program feature operational trade-off, visibility, and correctness convenience is not the only criterion
schema change normalization, keys, and operational impact structure before syntax
aggregate mismatch grouping grain and duplicate multiplication rows before totals

Query reasoning order

    flowchart TD
	  Rows["Build Row Set"] --> Filter["Apply Filters"]
	  Filter --> Group["Group / Aggregate if needed"]
	  Group --> Select["Project / Select List"]
	  Select --> Plan["Check Index and Plan Fit"]
	  Plan --> Outcome["Result Shape and Runtime Behavior"]

Query-order reminders

Topic Fast rule
alias in WHERE not normally available because filtering happens earlier
ORDER BY final presentation step, not a filter
LIMIT applies after ordering logic, not before result construction
DISTINCT removes duplicate rows after select-list expressions are formed
aggregate in WHERE aggregate filtering belongs in HAVING

Query and NULL traps

Trap Better reading
col = NULL use IS NULL
NOT IN with possible nulls prefer NOT EXISTS when nulls may appear
filtering right-side rows of a LEFT JOIN in WHERE can erase the preserved rows
COUNT(col) counts every row it counts non-null values only
equality intuition works with nullable expressions NULL breaks ordinary comparison logic

Safer outer-join filter placement

1SELECT a.id, b.status
2FROM a
3LEFT JOIN b
4  ON b.a_id = a.id
5 AND b.status = 'ACTIVE';

Join and grouping traps

Trap Better reading
joining before checking grain duplicated rows can distort aggregates
using LEFT JOIN then filtering the right table in WHERE often turns the result into inner-join behavior
grouping after a many-to-many join without intent totals can inflate silently
GROUP BY seems wrong so add DISTINCT blindly fix row logic first, not output cosmetics

Aggregate reminders

Aggregate Fast rule
COUNT(*) counts rows
COUNT(col) counts non-null values
COUNT(DISTINCT col) distinct non-null values only
SUM / AVG ignore null inputs
MIN / MAX depend on datatype ordering, not only numeric intuition

Set operations and subqueries

Need Better first fit
presence test EXISTS
anti-match with null risk NOT EXISTS
simple membership set IN
combine result sets without duplicates UNION
combine result sets with duplicates preserved UNION ALL
compare per-row derived facts scalar or correlated subquery with exact row-shape reasoning

Set-operation and subquery traps

Trap Better reading
scalar subquery can return any number of rows scalar comparison expects one value
UNION keeps duplicates UNION removes duplicates; UNION ALL keeps them
IN and EXISTS are always interchangeable null handling and correlation can change meaning
adding nesting for clarity more layers can hide row-shape mistakes

Indexing and EXPLAIN

When indexes help most Why
selective predicates fewer rows to scan
join keys faster row matching
ordered access plus LIMIT avoids extra sort work when the plan can use the index
When indexes help less Why
low-cardinality columns optimizer may prefer a scan
non-sargable predicates the index cannot be used efficiently
implicit type mismatch can break index usage and plan quality

Fast reminders

1EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
2SHOW INDEX FROM orders;

Index traps

Trap Better reading
function-wrapped indexed column in WHERE often non-sargable
assuming an index always helps cardinality and workload still matter
seeing a full scan and adding indexes blindly check predicate shape and data types first
indexing every column touched by a query write cost, storage, and low-cardinality columns matter too
composite index order ignored leftmost prefix and predicate shape still matter
implicit conversion overlooked datatype mismatch can ruin index use

EXPLAIN reminders

Question Better instinct
why is a scan happening? inspect predicate shape, cardinality, and datatype
why is order/sort expensive? see whether an index can support the requested order
why is join cost high? inspect join key indexing and row-count estimates
why is a query still slow after indexing? plan quality, query shape, and concurrency may still be the issue

Schema design chooser

Requirement Stronger first fit
enforce uniqueness primary key or unique constraint
preserve referential integrity foreign key
reduce duplication and update anomalies normalize appropriately
accelerate a frequent lookup path targeted index
support JSON flexibility with query speed generated column plus index when needed
repeated derived logic view or stored program only when the operational trade-off is worth it

Schema design traps

Trap Better reading
denormalize before proving the need start with correctness and maintainability
add foreign keys without thinking about delete/update behavior referential rules affect operations
every reporting need should drive schema change sometimes query or index changes are enough
using trigger logic to fix poor app design by default hidden side effects complicate operations

Transactions and locking

Concern Better rule
multi-step consistency use a transaction
concurrency pressure keep transactions short
visibility questions isolation matters
contention locking behavior affects throughput and correctness
multi-step business update keep related writes in one intentional transaction
read behavior under concurrent writes isolation and statement timing both matter

Fast reminder

1START TRANSACTION;
2UPDATE accounts SET balance = balance - 10 WHERE id = 1;
3UPDATE accounts SET balance = balance + 10 WHERE id = 2;
4COMMIT;

Transaction traps

Trap Better reading
long transaction around too much work more lock contention and risk
treating isolation like trivia visibility rules affect correctness
forgetting transaction boundaries when debugging concurrency scope matters as much as the statement
autocommit assumptions go unquestioned know when the statement is its own transaction
deadlock blamed on one statement only interaction between sessions matters

Isolation and concurrency reminders

Topic Fast rule
dirty read reading uncommitted changes
non-repeatable read same row read twice changes between reads
phantom repeated range query returns new matching rows
lock wait other work is holding a needed lock
deadlock competing lock acquisition paths block each other

Concurrency troubleshooting order

Symptom Check first
blocked update open transaction and lock holder
inconsistent read result isolation level and timing
deadlock statement order across sessions
throughput collapse transaction length, hotspot rows, and index fit

Stored programs and views

Feature Strong reason to use it Main caution
view reusable query surface can hide complexity
stored procedure packaged server-side logic deployment and debugging trade-offs
trigger automatic rule or side effect hidden work and performance/debugging cost
event scheduler timed database-side task operational visibility and ownership still matter

Stored-program traps

Trap Better reading
using a trigger for every business rule hidden behavior can hurt observability and performance
stored procedure always means better design packaging logic has debugging and deployment cost
view hides complexity so complexity is solved it may only move it
scheduled job means ops is handled ownership, logging, retries, and alerting still matter

JSON and generated columns

Need Better answer
extract a JSON attribute JSON extraction function
query that attribute efficiently often generated column plus index
keep the query optimizer happy make the predicate as sargable as possible
validate mixed-schema content carefully flexible JSON still needs application and query discipline
search repeated nested facts flatten or derive queryable attributes intentionally

Fast reminder

1SELECT JSON_EXTRACT(payload, '$.customer.id') AS customer_id
2FROM events;

JSON traps

Trap Better reading
JSON means indexing is irrelevant repeated access paths still need design
store everything in JSON for agility flexibility can trade away constraint clarity and query simplicity
extraction in every query is fine forever frequent access may justify generated columns and indexes
JSON path bug blamed on the optimizer validate path logic and datatype first

Decision order that usually wins

  1. Classify the problem as SQL correctness, schema fit, optimizer/index use, transaction behavior, stored program trade-off, or JSON access.
  2. Trace row shape before totals or output.
  3. Fix predicate and grouping logic before adding indexes.
  4. Keep transaction scope short and intentional.
  5. Prefer simpler, more observable database designs over clever hidden behavior.

Last 15-minute review

If you only keep one thing from each lane… Remember this
SQL correctness joins and NULL change row shape before anything else
indexing sargability often decides whether an index can help
EXPLAIN use evidence, not intuition
transactions short, deliberate scopes reduce contention
JSON generated columns often bridge JSON convenience and indexability
grouping wrong grain produces wrong totals
stored programs hidden behavior raises debugging and ops cost

What strong 1Z0-909 answers usually do

  • trace row-count changes before trusting grouped or filtered output
  • match index and EXPLAIN decisions to the actual predicate shape
  • treat transaction and locking behavior as correctness questions, not trivia
  • prefer the simplest query or schema choice that preserves correctness and performance
  • separate schema design benefits from operational side effects
  • choose observable, maintainable solutions before clever database-side magic
Revised on Sunday, May 10, 2026
Study Plan
Sample Questions