The Checklist I Run on Every MyBatis Query Before It Ships

Nothing warns you at compile time that your WHERE clause skips every index. A query with a typo fails loudly; a query with a bad execution plan just runs slowly, quietly, until the table is big enough to notice. This is the checklist I run on myself immediately after writing or changing any MyBatis query, while the intent is still fresh.

Step 1 — Look up metadata for every table first

Extract every table from the query — FROM, JOIN, INSERT INTO, UPDATE, and tables hiding inside subqueries, which are the easiest to miss. For each one, check its indexes, PK/UNIQUE constraints, and the real data_type of any column headed into a function call.

Step 2 — The function-signature check

For every SQL function call — TO_CHAR, TO_DATE, CAST, SUBSTRING, LPAD, concatenation, arithmetic — confirm the argument types actually exist as an overload in your database engine. This one bites people migrating from Oracle to PostgreSQL specifically.

-- fails at runtime if REG_DT is varchar 'YYYYMMDD'
TO_CHAR(REG_DT, 'YYYY.MM.DD')

-- varchar -> date -> formatted text
TO_CHAR(TO_DATE(REG_DT, 'YYYYMMDD'), 'YYYY.MM.DD')

Step 3 — Analyze every dynamic SQL branch, not just the one you tested

A query with <if>/<choose>/<foreach>/<where>/<set> is really N different queries wearing one id. Write out the actual SQL for every branch and check index usage per branch — the branch with the indexed condition may be fast while the branch without it does a full table scan. You tested one branch in dev; production runs all of them.

• A skipped <if> that removes the only indexed condition creates a full-scan branch — decide whether that branch is ever hit with real data volume.
• <foreach> IN-lists with 100+ items are worth reconsidering as a temp-table JOIN instead.

Step 4 — Position of user-defined functions

A function in the WHERE clause runs on every row before filtering — the index on that column can't help. The same function in the SELECT list only runs on rows that already survived the WHERE. Moving a function from WHERE to SELECT (or restructuring the filter) can change cost by orders of magnitude.

Step 5 — When to escalate to EXPLAIN ANALYZE

Even when every static check passes, flag the query for a deeper look when any of these is true: three or more dynamic SQL branches, a user-defined function in WHERE, four or more joined tables, or a large read with no LIMIT. Self-review catches structural mistakes; it can't predict what the query planner will actually do against production-sized data.