<p>MySQL raises ER_WARN_INDEX_NOT_APPLICABLE when the optimizer cannot use a specified index because of data type, collation, or prefix mismatches.</p>
<p>MySQL Error 1739: ER_WARN_INDEX_NOT_APPLICABLE occurs when the optimizer skips an index due to incompatible column type or collation. Align column types, collations, or adjust the query to resolve the warning.</p>
Cannot use %s access on index '%s' due to type or
MySQL displays the warning "Cannot use %s access on index '%s' due to type or collation conversion on field '%s'" (SQLSTATE HY000, error 1739) when the optimizer decides that the chosen index cannot be used efficiently. Incompatible data types, collations, or index prefix lengths make the access path impossible, so MySQL falls back to a less efficient scan.
The message is a warning, not a fatal error, but ignoring it can slow queries dramatically. Understanding why the index is unusable helps you restore performance and ensure predictable execution plans.
The warning typically arrives during SELECT, UPDATE, or DELETE statements that include WHERE, JOIN, or ORDER BY clauses referencing indexed columns. It is common after schema changes, charset migrations, or when comparing differently typed columns.
Although MySQL completes the statement, it logs the warning in SHOW WARNINGS and increments the Slow_queries counter if the degraded plan crosses the long_query_time threshold.
Ignoring the warning can hide serious performance regressions. A single full table scan instead of an indexed lookup can multiply execution time and resource usage, affecting application latency and database load.
Fixing the underlying mismatch restores index usage, reduces I/O, and keeps your queries within acceptable performance budgets, especially on large production tables.
Comparing VARCHAR to INT or mixing signed and unsigned integers prevents the optimizer from applying the index because implicit conversion disables range or ref access types.
Joining or filtering columns with different collations forces MySQL to convert values at runtime, making any index on the converted side unusable.
An index created with a prefix (e.g., INDEX(col(10))) may not cover the full comparison used in the query, so MySQL disregards it.
Wrapping an indexed column in a function (LOWER(), CAST(), DATE()) changes the expression, so the raw index cannot satisfy the predicate without a functional index.
Comparing a character column to a numeric literal causes a type cast that defeats the textual index.
Occurs when collation differences prevent comparison. Unlike 1739, it stops execution.
Indicates an index cannot be created because column types are unsuitable, rather than being skipped at runtime.
Happens during type conversion in string operations, often preceding index warnings.
MySQL may ignore FORCE INDEX hints due to the same incompatibility factors that trigger 1739.
Only the statements that rely on the unusable index suffer. Others using different predicates remain unaffected.
In small datasets you might not notice the impact, but the same query can explode in production. Treat it early.
Run SHOW WARNINGS immediately after the statement, or inspect the error log if --log_error_verbosity is high enough.
Galaxy highlights optimizer warnings inline, suggests matching collations, and lets you refactor queries with its AI copilot, preventing index misuse before code reaches production.
.avif)
.avif){kind=logo}
