Answer
Functions prevent MySQL from using indexes because they alter column values, forcing full scans.
Quick Answer
Functions in WHERE clause prevent index usage on that column. WHERE YEAR(created_at) = 2024 causes a full scan because MySQL must evaluate YEAR() for every row. Rewrite to use range: WHERE created_at BETWEEN "2024-01-01" AND "2024-12-31" - this uses the index on created_at. Similarly: WHERE LOWER(email) = "alice" won't use an index. Use functional indexes (MySQL 8) or store pre-computed values.
S
SugharaIQ Editorial Team
Verified Answer
This answer has been peer-reviewed by industry experts holding senior engineering roles to ensure technical accuracy and relevance for modern interview standards.
Want to bookmark, take notes, or join discussions?
Sign in to access all features and personalize your learning experience.
Source: SugharaIQ