1. Using functions or expressions on indexed columns
When performing function operations, mathematical expressions, or type conversions on indexed columns in query conditions, indexes usually become invalid.
Examples:
- WHERE YEAR(create_time) = 2023 (using the YEAR function on a date-indexed column)
- WHERE price * 1.2 > 100 (performing mathematical operations on a numeric-indexed column)
- WHERE CAST(id AS CHAR) = ‘123’ (performing type conversion on an indexed column)
In such cases, the database cannot directly use the index to quickly locate data and has to perform a full table scan.
2. Using inequality operators (!=/<>), NOT IN, NOT EXISTS on indexed columns
These negative conditions may cause index invalidation, and the database tends to perform a full table scan instead of using the index.
Examples:
- WHERE status != 1
- WHERE id NOT IN (1, 2, 3)
3. Using OR to connect indexed columns with non-indexed columns
When one side of the OR condition is an indexed column and the other is a non-indexed column, the index may become invalid.
Example:
- WHERE id = 123 OR name = ‘test’ (id is an indexed column, name is a non-indexed column)
Because the database needs to handle both indexed and non-indexed column conditions simultaneously, it is difficult to efficiently use a single index.
4. Fuzzy queries starting with %
When using LIKE for fuzzy queries, if the matching pattern starts with % (e.g., %abc), the index will be invalid; if it ends with % (e.g., abc%) or contains % in the middle (e.g., a%bc), the index may be partially valid (only matching the prefix).
Examples:
- WHERE name LIKE ‘%john’ (index is invalid)
- WHERE name LIKE ‘john%’ (index may be valid)
5. Subsequent indexed columns become invalid after range queries on preceding indexed columns
Composite indexes (multi-column indexes) follow the “leftmost prefix matching principle”. If a preceding column uses a range query (>, <, >=, <=, BETWEEN, etc.), subsequent indexed columns cannot be utilized.
Example: Composite index (age, name)
- WHERE age > 30 AND name = ‘john’ (range query on age causes name index to be invalid)
6. Implicit type conversion leading to index invalidation
When the type of the indexed column does not match the type of the value in the query condition, the database will perform implicit conversion, which may invalidate the index.
Example: Indexed column id is of INT type
- WHERE id = ‘123’ (the string ‘123’ is implicitly converted to INT, causing the index to be invalid)
7. Indexed columns contain NULL values and query conditions include IS NULL/IS NOT NULL
Most databases’ indexes do not store NULL values (or handle NULL specially). If an indexed column has a large number of NULL values, using IS NULL or IS NOT NULL may invalidate the index.
8. Too small table data volume or low index selectivity
- When the amount of data in the table is extremely small (e.g., only dozens of rows), a full table scan may be faster than an index query, so the database will automatically ignore the index.
- If index selectivity (proportion of non-repeating values) is too low (e.g., a gender column with only “male/female”), the index becomes meaningless, and the database may choose a full table scan.
9. Using NOT to negate combined conditions
For example, NOT (a = 1 AND b = 2). Complex negation logic may prevent the database from optimizing index usage.
