Practice Questions — Indexes and Query Performance

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100));
INSERT INTO users VALUES (1,'Aarav Sharma','aarav@example.com'),
                        (2,'Priya Nair','priya@example.com');

-- Before index
EXPLAIN SELECT * FROM users WHERE email = 'aarav@example.com';
-- type=ALL, rows=2

CREATE INDEX idx_users_email ON users(email);

-- After index
EXPLAIN SELECT * FROM users WHERE email = 'aarav@example.com';
-- type=ref, key=idx_users_email, rows=1
-- Explanation: B-Tree lookup replaces full scan; on a big table this is 1000x+ faster.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  amount DECIMAL(10,2),
  status VARCHAR(20)
);

CREATE INDEX idx_orders_cust_date
  ON orders(customer_id, order_date);

-- Test:
EXPLAIN SELECT * FROM orders
 WHERE customer_id = 101 AND order_date >= '2026-01-01';
-- type=range, key=idx_orders_cust_date

-- Bad (full scan)
SELECT * FROM orders
WHERE YEAR(order_date) = 2026 AND MONTH(order_date) = 4;

-- Good (range scan on the B-Tree)
SELECT * FROM orders
WHERE order_date >= '2026-04-01'
  AND order_date <  '2026-05-01';
-- Explanation: the range predicate keeps order_date bare,
-- so the B-Tree on order_date can be traversed directly.
-- The old version forced YEAR() and MONTH() on every row.

CREATE INDEX idx_orders_status_cust_amt
  ON orders(status, customer_id, amount);

-- EXPLAIN should show 'Using index' in Extra
EXPLAIN
SELECT customer_id, SUM(amount)
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;
-- Expected: type=ref, key=idx_orders_status_cust_amt, Extra='Using index'
-- Fully covering: no row lookup, aggregation happens on the index.

-- Diagnosis:
-- type=ALL -> full table scan (no index used for WHERE)
-- rows=2M -> large scan
-- 'Using filesort' -> separate sort step, not index-provided

-- Fix: composite index that both filters (level) and pre-sorts (created_at DESC)
CREATE INDEX idx_logs_level_created
  ON logs(level, created_at DESC);

-- After:
-- type=ref, rows='a few thousand', no filesort (backward index scan).
-- Query time drops from seconds to milliseconds.

-- idx_a is redundant: any query on 'a' alone can use idx_ab (leftmost prefix).
-- idx_b stays: needed for queries on b alone.
-- idx_ab stays: needed for queries on (a, b).

DROP INDEX idx_a ON t;

-- Verification:
-- EXPLAIN SELECT * FROM t WHERE a = 5;
-- Should still show type=ref, key=idx_ab
-- Saves disk + speeds up writes to 't'.

-- Leading wildcard LIKE kills any B-Tree index on body.
-- Full scan examines every row.

-- Fix: FULLTEXT index with natural-language search
CREATE FULLTEXT INDEX idx_posts_body ON posts(body);

-- Query becomes:
SELECT id, title
FROM posts
WHERE MATCH(body) AGAINST('django' IN NATURAL LANGUAGE MODE)
LIMIT 20;

-- EXPLAIN shows type=fulltext.
-- Supports phrase search, relevance ranking, stopword handling.
-- Caveats: min token length (default 4 chars, configurable),
-- InnoDB stopword list, requires rebuilding index after bulk load.

-- Step 1: Baseline EXPLAIN
-- type=ALL, rows=50M, Extra='Using where; Using temporary; Using filesort'
-- Dashboard takes 15+ seconds.

-- Step 2: Analyze the query
-- Filter: status + order_date (two columns, mixed equality/range)
-- Group: customer_id
-- Sort: SUM(amount) DESC (can't be served by an index -- aggregate result)
-- Project: customer_id, amount

-- Step 3: Design an index
-- Put equality first (status), then range (order_date). Include customer_id
-- and amount for covering, so aggregation doesn't need row lookups.

CREATE INDEX idx_orders_status_date_cust_amt
  ON orders(status, order_date, customer_id, amount);

-- Step 4: Re-run EXPLAIN
-- type=range, key=idx_orders_status_date_cust_amt
-- rows=~500k (filtered down from 50M)
-- Extra='Using where; Using index; Using temporary; Using filesort'
-- The 'Using temporary' and 'Using filesort' remain because the ORDER BY
-- is on the AGGREGATE SUM(amount), not the raw columns -- that's unavoidable.
-- But the 100x reduction in scanned rows takes the query from 15s to ~200ms.

-- Step 5: Verify with EXPLAIN ANALYZE to confirm actual timing.