A subtle function-on-column mistake forces a full table scan despite a perfectly good index.
Codesql
-- orders table has 500M rows
CREATE INDEX idx_orders_created ON orders (created_at);
-- Goal: fetch the latest 100 orders placed on a given day
SELECT order_id, customer_id
FROM orders
WHERE DATE(created_at) = '2024-01-15'
ORDER BY created_at DESC
LIMIT 100;
The query runs fine on a small dev dataset but does a full table scan in production. What is the bug?