The query is not using an index. Which two methods can be used to allow the query to use an index?

Question

Options

• AChange the WHERE clause to Birthday BETWEEN 1980-01-01 AND 1980-12-31.
• BAdd a functional index for YEAR(Birthday).
• CExecute ANALYZE TABLE to update the index statistics.
• DAdd a generated column calculating YEAR(Birthday) and index that column.
• EAdd FORCE INDEX (Birthday) to the query.

• A
  72% (36)
• B
  8% (4)
• C
  16% (8)
• D
  4% (2)

Explanation

Rewriting the WHERE clause as a range scan (A) removes the function wrapper from the Birthday column, enabling MySQL's optimizer to use the existing index directly - a function like YEAR() applied to a column prevents index usage because the index stores raw column values, not computed results. Adding FORCE INDEX (E) is a query hint that overrides the optimizer and compels use of the named index regardless of its cost estimate, making the query physically use the index even when the optimizer would otherwise skip it.

Why the distractors are wrong:

• B - A functional index on YEAR(Birthday) is a valid MySQL 8.0+ feature, but it requires a DDL schema change and creates a new index, rather than allowing the existing Birthday index to be used; this exam tests solutions to the current query.
• C - ANALYZE TABLE refreshes cardinality statistics to help the optimizer choose among existing indexes, but it cannot make a function-wrapped column eligible for index lookup - that's a structural problem, not a statistics problem.
• D - A generated column + index requires both a schema change and the optimizer to recognize YEAR(Birthday) as equivalent to the stored column; this is not guaranteed without a query rewrite, making it unreliable as a standalone fix.

Memory tip: "Functions break indexes" - any time you wrap a column in a function in a WHERE clause, the optimizer can't use that column's B-tree index. Your fix options are: remove the function (rewrite as BETWEEN) or override the optimizer (FORCE INDEX).

No community discussion yet for this question.