Lesson 25 — Query Performance

Indexes

An index is a data structure that speeds up lookups on a column. Without indexes, the database scans every row. With them, it jumps directly to matching rows.

SQL — Creating indexes 
-- CREATE INDEX:
CREATE INDEX idx_learners_city
    ON learners (city);

CREATE INDEX idx_learners_course_mark
    ON learners (course, mark);   -- composite index

CREATE UNIQUE INDEX idx_learners_email
    ON learners (email);

-- DROP INDEX:
DROP INDEX idx_learners_city ON learners;  -- MySQL
DROP INDEX idx_learners_city;               -- PostgreSQL

-- See existing indexes:
SHOW INDEX FROM learners;        -- MySQL
SELECT * FROM pg_indexes WHERE tablename = 'learners'; -- PostgreSQL
SQL — When indexes help 
-- When does an index help?
-- ✓ WHERE city = 'Johannesburg'          -- equality on indexed column
-- ✓ WHERE city = 'JHB' AND mark >= 70    -- composite index (city, mark)
-- ✓ ORDER BY city                        -- sorted scan avoids full sort
-- ✓ JOIN ON l.learner_id = a.learner_id  -- FK indexes are essential

-- When does an index NOT help?
-- ✗ WHERE UPPER(city) = 'JOHANNESBURG'   -- function on indexed column
-- ✗ WHERE city LIKE '%burg'              -- leading wildcard
-- ✗ Low-cardinality column (TRUE/FALSE)  -- only 2 values = no benefit
-- ✗ Very small tables (< a few hundred rows) — full scan is faster

-- EXPLAIN / QUERY PLAN — see how the optimiser uses indexes:
EXPLAIN SELECT * FROM learners WHERE city = 'Johannesburg';
-- Look for: type=ref (uses index) vs type=ALL (full table scan)
SQL — Full-text index 
-- Full-text index for LIKE searches on text:
ALTER TABLE learners
    ADD FULLTEXT INDEX idx_ft_name (full_name);

-- Full-text search (MySQL):
SELECT full_name
FROM   learners
WHERE  MATCH(full_name) AGAINST ('Mokoena');
-- Far faster than WHERE full_name LIKE '%Mokoena%' on large tables

Practice Task

Your Turn

Given a learners table with 100,000 rows: identify which columns should have indexes and explain why. Write the CREATE INDEX statements. Use EXPLAIN on a SELECT with a WHERE clause on city before and after adding the index. Describe what changes in the EXPLAIN output.

Common Mistakes

  • Too many indexes — every index slows INSERT, UPDATE, DELETE (index must be maintained). Index only columns you actually filter or join on.
  • Indexing low-cardinality columns (boolean, active/inactive) — the optimiser often ignores them.
  • Function on indexed column in WHERE: WHERE UPPER(city) = 'JOBURG' cannot use the index on city.
  • Forgetting to index FK columns — JOINs without indexes on FK columns are dramatically slower.

Professional Tip

Always index foreign key columns. If you forget everything else about indexes, remember this: every FK column needs an index, or your JOINs will degrade to full table scans as data grows.

Mini Quiz

Which query pattern prevents an index from being used?