Indexes & Performance

-- With no index on roll_number, this reads every row in the table
SELECT name, branch FROM students WHERE roll_number = 'CS2026114';

-- Create one. Name it so anybody can tell what it covers.
CREATE INDEX idx_students_roll_number ON students(roll_number);

-- Same query, same result, a completely different amount of work
SELECT name, branch FROM students WHERE roll_number = 'CS2026114';

-- What indexes does this table already have?
SHOW INDEX FROM students;                 -- MySQL
-- PostgreSQL: SELECT * FROM pg_indexes WHERE tablename = 'students';
-- SQL Server: EXEC sp_helpindex 'students';

-- These two are indexed for you
CREATE TABLE students (
    id          INT AUTO_INCREMENT PRIMARY KEY,   -- indexed automatically
    email       VARCHAR(120) UNIQUE,              -- indexed automatically
    roll_number VARCHAR(20),
    branch      VARCHAR(40),
    joined_on   DATE
);

-- The foreign key column is the one that needs thought
CREATE TABLE orders (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    ordered_at DATETIME,
    amount     DECIMAL(10,2),
    FOREIGN KEY (student_id) REFERENCES students(id)
);
-- MySQL/InnoDB indexes student_id for you.
-- PostgreSQL does not, so there you would also write:
--   CREATE INDEX idx_orders_student_id ON orders(student_id);

-- Adding, and then removing, where the dialects diverge
CREATE INDEX idx_students_branch ON students(branch);

-- MySQL needs the table name in order to drop an index
DROP INDEX idx_students_branch ON students;
ALTER TABLE students DROP INDEX idx_students_branch;   -- the same thing

-- PostgreSQL: DROP INDEX idx_students_branch;
-- SQL Server: DROP INDEX idx_students_branch ON students;

CREATE INDEX idx_students_branch_joined
    ON students(branch, joined_on);

-- Uses the index: filters on the leftmost column
SELECT name FROM students WHERE branch = 'CSE';

-- Uses it fully: equality on the first column, range on the second
SELECT name FROM students
WHERE branch = 'CSE'
  AND joined_on >= '2026-01-01';

-- Cannot use it: joined_on is not the leftmost column
SELECT name FROM students WHERE joined_on >= '2026-01-01';
-- That query needs an index of its own:
--   CREATE INDEX idx_students_joined ON students(joined_on);

-- Covering: every column the query needs lives inside the index,
-- so the table itself is never touched
SELECT branch, joined_on FROM students WHERE branch = 'CSE';

-- Redundant. The composite above already starts with branch.
-- CREATE INDEX idx_students_branch ON students(branch);

-- Function on the column: correct, and slow
SELECT id FROM orders WHERE YEAR(ordered_at) = 2026;

-- The same question, asked so the index can answer it
SELECT id FROM orders
WHERE ordered_at >= '2026-01-01'
  AND ordered_at <  '2027-01-01';

-- Still index-friendly, because the function is on the constant
SELECT id FROM orders
WHERE ordered_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- Prefix search: usable.  Substring search: not usable.
SELECT name FROM students WHERE name LIKE 'An%';
SELECT name FROM students WHERE name LIKE '%an%';

-- Quote text values, even when they look like numbers
SELECT * FROM students WHERE roll_number = '20261140';   -- good
-- SELECT * FROM students WHERE roll_number = 20261140;  -- silent scan

-- OR across two different columns often uses neither index.
-- Two indexed lookups combined with UNION frequently beat it:
SELECT id, name FROM students WHERE email       = 'ananya@example.com'
UNION
SELECT id, name FROM students WHERE roll_number = 'CS2026114';

-- The plan, without running the query
EXPLAIN SELECT name FROM students WHERE branch = 'CSE';

-- MySQL 8.0.18 and later: actually run it and report real timings
-- EXPLAIN ANALYZE SELECT name FROM students WHERE branch = 'CSE';

-- A fuller, structured version (MySQL)
EXPLAIN FORMAT=JSON
SELECT s.name, o.amount
FROM students s
JOIN orders o ON o.student_id = s.id
WHERE s.branch = 'CSE';

-- Refresh the statistics the planner reasons from
ANALYZE TABLE students;          -- MySQL
-- ANALYZE students;             -- PostgreSQL

-- Which queries are even worth looking at? Let the server tell you.
-- MySQL slow query log:
--   SET GLOBAL slow_query_log  = 'ON';
--   SET GLOBAL long_query_time = 1;      -- seconds

-- A bulk load pattern: build the index once, at the end
-- ALTER TABLE orders DROP INDEX idx_orders_student_id;
--   ... load a few million rows ...
-- CREATE INDEX idx_orders_student_id ON orders(student_id);

-- Which indexes is nobody using? (MySQL 8.0, sys schema)
SELECT * FROM sys.schema_unused_indexes;

-- PostgreSQL: idx_scan counts how often the index was used
-- SELECT relname, indexrelname, idx_scan
-- FROM pg_stat_user_indexes
-- ORDER BY idx_scan;

-- Redundant pair: the composite already covers branch on its own
-- CREATE INDEX idx_students_branch        ON students(branch);
-- CREATE INDEX idx_students_branch_joined ON students(branch, joined_on);

-- Fix the query before reaching for an index
SELECT id, name FROM students WHERE branch = 'CSE' LIMIT 50;
-- rather than
-- SELECT * FROM students WHERE branch = 'CSE';