Table Has an Index but Queries Still Full Table Scan: What’s the Cause?
Suppose you have a users table with 5 million records. Both the created_at and username columns have proper B-Tree indexes. Every query by id or exact search responds in under 5ms.
The problem arises when you need to filter data by registration year or perform case-insensitive username searches:
-- Find users registered in 2023
SELECT id, full_name, email FROM users
WHERE YEAR(created_at) = 2023;
-- Case-insensitive account search
SELECT id, username FROM users
WHERE LOWER(username) = 'nguyenvana';
Execution time immediately jumps from a few milliseconds to 4–6 seconds. MySQL scans all 5 million rows. Server CPU spikes to 100% when multiple queries run concurrently.
Why does the database ignore the existing index? By nature, traditional B-Tree indexes only store the column’s raw values. An index does not store the computed result after a column has been evaluated by a function.
The Technical Root Cause: Non-Sargable Queries and Full Table Scans
In database optimization, the two statements above belong to the category of non-sargable queries (Search Argument Able). This means the query optimizer cannot use the index tree to narrow down the search range.
The underlying mechanics are straightforward:
- The index tree for
created_atonly contains raw datetime values such as2023-05-12 08:30:00. It does not pre-store the number2023. - When wrapping the column in the
YEAR()function, MySQL is forced to read every row from disk. Then, the CPU computes the function 5 million times to produce values for comparison.
You can verify this using the EXPLAIN statement:
EXPLAIN SELECT id, full_name, email FROM users
WHERE YEAR(created_at) = 2023;
The type column shows ALL, while the key column returns NULL. Clearly, the original index has been completely bypassed.
Traditional Workarounds and Their Limitations
Before MySQL 8, developers typically chose one of two workarounds:
1. Rewriting Query Conditions as Range Queries
For datetime data types, the cleanest approach is to keep the original column untouched and filter by a range:
SELECT id, full_name, email FROM users
WHERE created_at >= '2023-01-01 00:00:00'
AND created_at < '2024-01-01 00:00:00';
This solution leverages the existing index immediately. However, it only works for numeric or date/time values. It cannot help with string parsing, complex expression calculations, or JSON extraction.
2. Creating a Generated Column and Indexing It
Since MySQL 5.7, a common workaround has been adding a virtual column:
-- Create a virtual column storing the account creation year
ALTER TABLE users
ADD COLUMN created_year INT GENERATED ALWAYS AS (YEAR(created_at)) VIRTUAL;
-- Index the virtual column
CREATE INDEX idx_users_created_year ON users(created_year);
Query performance improves dramatically. In return, the table schema becomes cluttered. The backend team must also update application code to query the new column.
The Optimal Solution: Functional Indexes in MySQL 8
Starting with version 8.0.13, MySQL supports Functional Indexes (expression indexes). This feature allows you to index the result of an expression directly, without adding new columns to your table.
Under the hood, MySQL 8 creates a hidden virtual column at the storage layer and builds a B-Tree index on it. Your public table schema remains completely clean.
Functional Index Syntax
The indexed expression must be enclosed in double parentheses (()):
-- Index the year expression
CREATE INDEX idx_users_year_created
ON users ((YEAR(created_at)));
-- Index lowercase strings
CREATE INDEX idx_users_lower_username
ON users ((LOWER(username)));
The outer parentheses belong to the CREATE INDEX syntax, while the inner parentheses enclose the expression to be indexed.
Real-World Performance Benchmarks
Keep the original query unchanged and check it again with EXPLAIN:
EXPLAIN SELECT id, full_name, email FROM users
WHERE YEAR(created_at) = 2023;
The metrics show a striking improvement:
- The
keycolumn now uses theidx_users_year_createdindex. - The
typecolumn changes fromALLtoref. - The scanned row count (
rows) drops from 5,000,000 down to around 120,000. - Execution time plummets from 4.2 seconds to just 3 milliseconds.
Most importantly: you don’t have to touch a single line of backend application code.
Accelerating Queries on JSON Data
Functional indexes are exceptionally useful when working with JSON columns. Suppose your table has a metadata column storing device details: {"device": {"os": "iOS", "version": 16}}.
Because JSON columns cannot be indexed directly, you must cast the data using the CAST function:
-- Index the os field in JSON
CREATE INDEX idx_user_os
ON users ((CAST(metadata->>'$.device.os' AS CHAR(20))));
-- Query: the WHERE expression must match the index definition exactly
SELECT id, full_name FROM users
WHERE CAST(metadata->>'$.device.os' AS CHAR(20)) = 'iOS';
3 Technical Considerations for Production
To prevent issues in production environments, keep these three rules in mind:
- Functions must be deterministic: The same input must always produce the exact same output. MySQL will reject index creation if you use non-deterministic functions that change at runtime, such as
NOW(),CURDATE(), orRAND(). - Write overhead: Every
INSERTorUPDATEoperation on the underlying columns triggers expression recomputation and B-Tree tree updates. Only index filter expressions that are queried frequently. - Expressions must match exactly: The optimizer only activates the index when the expression in the
WHEREclause matches the index definition character for character.
