MySQL Datetime Implicit Conversion Causing Full Table Scan

An indexed datetime column being ignored, resulting in a full table scan, is very often caused by comparing that column against a value MySQL has to implicitly convert before comparison — and that conversion, depending on exactly how it happens, can prevent the index from being used at all.

The Problem

A query filtering on an indexed datetime column runs unexpectedly slowly, and EXPLAIN confirms the index isn't being used:

CREATE INDEX idx_orders_created_at ON orders(created_at);

EXPLAIN SELECT * FROM orders WHERE created_at = '2026-07-28';
+----+-------------+--------+------+---------------+------+
| id | select_type | table  | type | possible_keys | key  |
+----+-------------+--------+------+---------------+------+
|  1 | SIMPLE      | orders | ALL  | idx_orders... | NULL |
+----+-------------+--------+------+---------------+------+

The possible_keys column even shows the index as a candidate, yet key shows NULL — MySQL considered the index but didn't actually use it.

Why It Happens

Comparing a DATETIME column against a plain date string, or against a value in an unexpected format, can trigger implicit type conversion or produce a comparison that doesn't align with how the index is actually structured, in a couple of related ways:

• Comparing a DATETIME column (which includes a time component) against a date-only string with = only matches rows at exactly midnight on that date, since the implicit conversion treats the string as midnight of that day — this isn't a lost-index problem specifically, but a very common logic bug that looks like a query "not finding" expected rows, closely related to the same root confusion
• Using a function on the column itself in the WHERE clause (like DATE(created_at) = '2026-07-28') prevents the index from being used at all, since the index is built on the raw column values, not on the result of applying a function to them
• Comparing against a string in a format MySQL can't cleanly and unambiguously convert to match the column's actual stored type sometimes forces a less efficient comparison path
• Mixing a numeric or string representation of a date inconsistently across different parts of an application, leading to comparisons that technically work but don't align well with how the query optimizer can use available indexes

The Fix

For matching an entire day's worth of datetime values, use a range comparison instead of applying a function to the column or relying on exact equality against a date-only string:

SELECT * FROM orders
WHERE created_at >= '2026-07-28'
  AND created_at < '2026-07-29';

This range comparison uses the index efficiently, since both boundary values are compared directly against the raw column, with no function wrapping the column itself — MySQL can use the index to jump directly to the relevant range rather than scanning every row.

Never wrap the indexed column itself in a function within the WHERE clause if avoiding it is possible — this is the single most common indexed-datetime performance mistake, since it silently defeats the index regardless of how well everything else is configured:

-- Avoid: function on the column prevents index use
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-28';

-- Prefer: range comparison, index-friendly
SELECT * FROM orders WHERE created_at >= '2026-07-28' AND created_at < '2026-07-29';

Confirm your application consistently passes properly formatted datetime strings (YYYY-MM-DD HH:MM:SS) to queries, rather than relying on MySQL's more permissive but potentially inconsistent implicit parsing of loosely formatted date strings, which can behave differently depending on the exact string format and MySQL version.

Verify the fix actually restores index usage by re-running EXPLAIN after adjusting the query:

EXPLAIN SELECT * FROM orders WHERE created_at >= '2026-07-28' AND created_at < '2026-07-29';
-- key column should now show idx_orders_created_at, not NULL

Still Not Working?

If the range comparison approach is already in use and the index is still being ignored, check whether the column's actual data type is something other than expected — comparing a DATETIME string against a column that's actually stored as VARCHAR (common in older or migrated schemas that never properly typed date columns) triggers implicit string-to-string comparison behavior with its own separate set of rules, rather than genuine datetime comparison, and no amount of range-query restructuring fixes an underlying column type that isn't actually a proper datetime type in the first place:

SHOW COLUMNS FROM orders LIKE 'created_at';

If this reveals the column is stored as text rather than a proper DATETIME/TIMESTAMP type, converting the column to the correct type is the actual underlying fix, rather than continuing to adjust query syntax around a fundamentally mistyped column.