✏️ Explanatory Question
Level: Advanced — An open-ended favourite; interviewers want a structured, practical checklist.
Query optimization is a systematic process of finding why a query is slow and then removing the bottleneck. The best answer follows a clear methodology rather than random tweaks.
type=ALL), missing indexes (key=NULL), and high row counts.WHERE, JOIN, ORDER BY, and GROUP BY.SELECT *: Fetch only the columns you need to reduce I/O and enable covering indexes.WHERE YEAR(date)=2026 blocks the index; use a range instead.WHERE to reduce rows before grouping/joining.ANALYZE TABLE so the optimizer has accurate data.WHERE clause. It forces MySQL to evaluate every row, defeating the index entirely.
WHERE YEAR(order_date) = 2026WHERE status != 'X' (negation)SELECT * everywhereLIKE '%text' (leading wildcard)WHERE order_date >= '2026-01-01'WHERE status = 'Active'SELECT id, nameLIKE 'text%' (trailing wildcard)-- BEFORE: function on column blocks the index (full scan)
SELECT * FROM orders WHERE YEAR(order_date) = 2026;
-- AFTER: sargable range condition uses the index
SELECT id, customer_id, amount
FROM orders
WHERE order_date >= '2026-01-01'
AND order_date < '2027-01-01';
-- Add a supporting index
CREATE INDEX idx_order_date ON orders(order_date);
-- Refresh optimizer statistics
ANALYZE TABLE orders;
-- Confirm the improvement
EXPLAIN SELECT id FROM orders
WHERE order_date >= '2026-01-01';