From the ai augmented sql sample test

When does an AI-suggested index hint help, and when does it hurt the query plan?

The item shows an AI-generated query that includes an explicit index hint — FORCE INDEX, USE INDEX, or WITH (INDEX(...)) depending on dialect — and asks the candidate to evaluate whether the hint helps, hurts, or is irrelevant. The competence under test is whether the candidate recognizes that index hints trade plan stability for plan flexibility, that the trade-off is rarely worth it, and that AI tools over-suggest hints because their training data is dominated by Stack Overflow answers in which someone needed a hint to escape a specific plan-regression incident. The probe tests whether the candidate critically evaluates the AI’s output instead of accepting it.

What this question tests

The concept being tested is the relationship between the cost-based optimizer’s plan choice, table statistics, and the narrow set of conditions under which a manual index hint produces better outcomes than letting the optimizer choose. Modern relational engines — PostgreSQL, MySQL, SQL Server, Oracle, Snowflake, BigQuery — pick query plans based on statistics about table sizes, value distributions, and index selectivity. When statistics are accurate and recent, the optimizer’s plan is almost always at least as good as any plan a developer could choose by hand. Index hints override this choice and pin the plan to whatever index the developer named, which:

  1. helps when the optimizer’s statistics are stale or its cost model is miscalibrated for an unusual query shape, and
  2. hurts when the optimizer’s plan would have improved automatically as data volumes or value distributions changed.

Hints produce locally-improved plans that decay over months as the underlying data shifts. AI assistants over-suggest hints because failure-mode questions (“why is this query slow”) on forums dominate training data over success-mode questions (“why is this query fast”). The probe tests whether the candidate recognizes the asymmetry.

Why this is the right answer

Consider an AI-generated query with a hint:

-- AI-generated MySQL: FORCE INDEX pinned to a specific index
SELECT o.order_id, o.customer_id, o.amount
FROM orders o FORCE INDEX (idx_orders_customer_id)
WHERE o.customer_id = 12345
  AND o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC
LIMIT 10;

The hint forces MySQL to use idx_orders_customer_id even if a composite index on (customer_id, order_date) exists and would serve the query better. If the composite index doesn’t exist today but is added later, the hint locks the query into the old, slower plan and prevents the engine from picking up the new index automatically.

A correctly-evaluated AI suggestion checks three things before accepting the hint:

  1. Does the hint name an index that exists? AI tools sometimes hallucinate index names. A query with a hint to a non-existent index errors on most engines (MySQL ignores the hint silently with a warning; SQL Server raises an error).
  2. Is the named index actually the best choice for this query, today and at projected data volume? The optimizer makes this judgment automatically; pinning the hint asserts that the developer’s judgment is more durable than the optimizer’s. That assertion is rarely correct.
  3. Is the hint covering up a deeper problem? A query that “needs” a hint usually has stale statistics, a missing index, or a query shape (OR chains, LIKE '%pattern%', non-sargable predicates) that defeats the optimizer. The hint masks the symptom; the underlying cause remains.

The right answer recognizes that the hint is almost always a signal to investigate further, not a signal to accept the suggestion as-is. The corrected query removes the hint and relies on the optimizer:

-- Corrected: let the optimizer choose
SELECT o.order_id, o.customer_id, o.amount
FROM orders o
WHERE o.customer_id = 12345
  AND o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC
LIMIT 10;

If the optimizer picks a slow plan, the right escalation is (a) check that statistics are fresh (ANALYZE TABLE on MySQL, ANALYZE on PostgreSQL, UPDATE STATISTICS on SQL Server), (b) verify the right indexes exist for the query shape, and (c) only then consider a hint as a temporary mitigation while the underlying issue is investigated.

What the wrong answers reveal

The three incorrect options each map to a common but mistaken mental model:

  • “Always use the AI-suggested hint — the AI knows the query plan.” AI tools do not know the query plan; they pattern-match against forum answers. Respondents picking this option over-trust generative output and underestimate the plan-stability cost.
  • “Hints are always wrong — strip them out and let the optimizer decide.” This over-corrects. There are real cases where the optimizer is wrong: parameter-sniffing bugs in SQL Server, edge-case queries on tables with skewed distributions, queries that run rarely enough that the optimizer never gathers good statistics. Respondents picking this option have absorbed a “no hints, ever” rule that ignores the legitimate use cases.
  • “The hint matters only when the index doesn’t exist; if the index exists, the hint is a no-op.” This is wrong twice. When the index exists, the hint forces its use even if a better index also exists. When the index doesn’t exist, the hint either errors or is silently ignored, depending on dialect. Respondents picking this option have a partial mental model that misses both edge cases.

The wrong-answer patterns cluster into three distinct gaps: AI over-trust, blanket rule against hints, and mechanical misunderstanding of how hints affect plan choice.

How the sample test scores you

In the AIEH 5-question AI-Augmented SQL sample, this item contributes one of five datapoints aggregated into a single ai_augmented_sql score via the W3.2 normalize-by-count threshold. Binary scoring per item: 5 for the correct option, 1 for any of the three wrong options. With 5 binary items, the average ranges 1–5 and the level threshold maps avg ≤ 2 to low, ≤ 4 to mid, > 4 to high.

Data Notice: Sample-test results are directional indicators only. A 5-question sample can’t reliably distinguish a candidate who reasons about query plans from one who got lucky on these specific items; for a verified Skills Passport credential, take the full 50-question assessment.

The full assessment probes optimizer behavior across dialects, covering index design, statistics maintenance, plan regression, and the legitimate-vs-illegitimate use cases for hints. See the scoring methodology for how AI-Augmented SQL scores map onto the AIEH 300–850 Skills Passport scale.

  • Reading EXPLAIN and EXPLAIN ANALYZE output. Every major engine surfaces the chosen plan, the access method per table, the estimated and actual row counts, and (in ANALYZE mode) the actual execution time. Reading the plan before adding a hint, and reading it again after, is the only reliable way to verify that the hint helped.
  • Statistics freshness as the more common root cause. When an AI-suggested hint appears to fix a slow query, the actual fix is often that running ANALYZE on the table refreshed statistics that had drifted. The hint and the statistics refresh frequently get conflated. Separating them — running ANALYZE first, retesting, and only then considering the hint — disambiguates which intervention actually mattered.
  • Plan-stability features as the modern alternative. SQL Server has Query Store, Oracle has SQL Plan Baselines, and PostgreSQL has the pg_hint_plan extension and stored plan formats. These features pin plans without inline hints, making them easier to remove later and more visible to operators. AI tools rarely suggest these alternatives.
  • Sargability as the upstream issue. Many “needs a hint” queries are non-sargable — they apply functions to indexed columns (WHERE LOWER(email) = 'x'), use leading wildcards (LIKE '%pattern%'), or perform arithmetic on indexed columns. Rewriting the query to be sargable usually beats any hint.

For the broader AI-Augmented SQL lineup, see the tests catalog. For how this skill connects to hiring decisions, see hire, assess, and the algorithms data structures prep guide for adjacent technical fluency probes. Candidates can also browse learn for foundational material.

Sources

  • ISO/IEC 9075-2:2023. Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation). International Organization for Standardization. — The standard does not specify hint syntax; vendor extensions govern.
  • MySQL Documentation. (2024). MySQL Index Hints. https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
  • Microsoft. (2024). SQL Server Documentation: Hints (Transact-SQL) — Table. https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table
  • Sackett, P. R., & Lievens, F. (2008). Personnel selection. Annual Review of Psychology, 59, 419–450. — Reviews evidence on validity coefficients for technical work-sample assessments, of which AI-generated-SQL critique is a contemporary form.

Try the question yourself

This explainer covers what the item measures. To see how you score on the full ai augmented sql family, take the free 5-question sample.

Take the ai augmented sql sample