Week 6 — Index Design & Query Performance¶

-- Check your server's page size
SHOW VARIABLES LIKE 'innodb_page_size';
-- Default: 16384 (16 KB)

-- Estimate index tree height for a table
SELECT
    TABLE_NAME,
    INDEX_NAME,
    STAT_VALUE AS leaf_pages
FROM mysql.innodb_index_stats
WHERE stat_name = 'n_leaf_pages'
  AND database_name = 'university';

-- Primary key: clustered, NOT NULL, unique
CREATE TABLE students (
    student_id   INT          NOT NULL AUTO_INCREMENT,
    email        VARCHAR(100) NOT NULL,
    first_name   VARCHAR(50)  NOT NULL,
    last_name    VARCHAR(50)  NOT NULL,
    dept_id      INT,
    gpa          DECIMAL(3,2),
    enrolled_on  DATE,
    is_active    TINYINT(1)   DEFAULT 1,
    PRIMARY KEY (student_id),
    UNIQUE KEY uq_students_email (email)
);

-- Composite primary key (junction table)
CREATE TABLE enrollments (
    student_id  INT  NOT NULL,
    course_id   INT  NOT NULL,
    semester    CHAR(6) NOT NULL,   -- e.g. '2024FA'
    grade       CHAR(2),
    PRIMARY KEY (student_id, course_id, semester)
);

-- Non-unique index for foreign key lookups
ALTER TABLE students
    ADD INDEX idx_students_dept (dept_id);

-- Composite index for common query pattern
ALTER TABLE enrollments
    ADD INDEX idx_enr_student_semester (student_id, semester);

-- Covering composite index for reporting
ALTER TABLE grades
    ADD INDEX idx_grades_cover (student_id, course_id, points, max_points);

-- Range scan on gpa index
EXPLAIN SELECT student_id, first_name, gpa
FROM students
WHERE gpa BETWEEN 3.5 AND 4.0;
-- type: range   key: idx_students_gpa   Extra: Using index condition

-- composite index (dept_id, gpa)
ALTER TABLE students ADD INDEX idx_dept_gpa (dept_id, gpa);

-- MySQL 8 may use skip scan even without dept_id in WHERE
EXPLAIN SELECT student_id, gpa
FROM students
WHERE gpa > 3.5;
-- Extra: Using index for skip scan

-- Without covering index: secondary index lookup + table lookup
SELECT first_name, last_name, gpa FROM students WHERE dept_id = 3;

-- Add covering index
ALTER TABLE students
    ADD INDEX idx_dept_cover (dept_id, last_name, first_name, gpa);

-- Now EXPLAIN shows: Extra: Using index  (no table lookup!)
EXPLAIN SELECT first_name, last_name, gpa
FROM students
WHERE dept_id = 3
ORDER BY last_name;

-- University query: students in a dept, sorted by last name
-- Best index: (dept_id, last_name, first_name)
ALTER TABLE students
    ADD INDEX idx_dept_name (dept_id, last_name, first_name);

SELECT student_id, first_name, last_name
FROM students
WHERE dept_id = 7
ORDER BY last_name, first_name;
-- Uses index for both filtering AND sorting — no filesort!

-- Measure selectivity before deciding to index
SELECT
    COUNT(DISTINCT dept_id)  / COUNT(*) AS dept_selectivity,     -- ~0.003 (low)
    COUNT(DISTINCT email)    / COUNT(*) AS email_selectivity,    -- ~1.000 (high)
    COUNT(DISTINCT gpa)      / COUNT(*) AS gpa_selectivity,      -- ~0.04  (medium)
    COUNT(DISTINCT last_name)/ COUNT(*) AS name_selectivity      -- ~0.15  (medium)
FROM students;

-- Query: enrollments for a student in a semester, ordered by course
-- Predicate: student_id = ? AND semester = ?  ORDER BY course_id
ALTER TABLE enrollments
    ADD INDEX idx_enr_stu_sem_crs (student_id, semester, course_id);

-- Full index information for a table
SHOW INDEX FROM students\G

-- Via information_schema (scriptable)
SELECT
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    CARDINALITY,
    INDEX_TYPE,
    NON_UNIQUE,
    NULLABLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'university'
  AND TABLE_NAME   = 'students'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- Force statistics recalculation (reads more pages for accuracy)
SET SESSION innodb_stats_transient_sample_pages = 100;
ANALYZE TABLE students, courses, enrollments;

-- Check when stats were last updated
SELECT TABLE_NAME, UPDATE_TIME, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'university';

-- Check fragmentation level
SELECT
    TABLE_NAME,
    DATA_LENGTH,
    INDEX_LENGTH,
    DATA_FREE,
    ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 1) AS frag_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'university'
ORDER BY frag_pct DESC;

-- Rebuild table + all indexes (locks table in MySQL 5.x, online in 8.x)
OPTIMIZE TABLE enrollments;

-- Alternative: ALTER TABLE ... ALGORITHM=INPLACE (online, non-blocking)
ALTER TABLE enrollments
    ENGINE = InnoDB,
    ALGORITHM = INPLACE,
    LOCK = NONE;

-- Make an index invisible (optimizer ignores it)
ALTER TABLE students
    ALTER INDEX idx_students_dept INVISIBLE;

-- Verify current visibility
SELECT INDEX_NAME, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'university'
  AND TABLE_NAME = 'students'
GROUP BY INDEX_NAME, IS_VISIBLE;

-- Force optimizer to use invisible indexes (for testing in this session only)
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

-- Make visible again if needed
ALTER TABLE students
    ALTER INDEX idx_students_dept VISIBLE;

-- Safe to drop after confirming no performance regression
DROP INDEX idx_students_dept ON students;

-- Problem: query uses YEAR() on a date column — can't use regular index
SELECT student_id FROM students
WHERE YEAR(enrolled_on) = 2023;  -- forces full table scan!

-- Solution 1: Functional index (MySQL 8.0.13+)
ALTER TABLE students
    ADD INDEX idx_enroll_year ((YEAR(enrolled_on)));

-- Now this uses the index:
EXPLAIN SELECT student_id FROM students
WHERE YEAR(enrolled_on) = 2023;

-- Solution 2: Functional index for case-insensitive search
ALTER TABLE students
    ADD INDEX idx_lastname_lower ((LOWER(last_name)));

SELECT student_id FROM students
WHERE LOWER(last_name) = 'chen';

-- Table storing student preferences as JSON
ALTER TABLE students
    ADD COLUMN pref_json JSON,
    ADD COLUMN major_code VARCHAR(10)
        AS (JSON_UNQUOTE(JSON_EXTRACT(pref_json, '$.major'))) VIRTUAL,
    ADD INDEX idx_major_code (major_code);

-- Insert with JSON
UPDATE students
SET pref_json = '{"major": "CS", "minor": "Math", "advisor_id": 42}'
WHERE student_id = 1001;

-- This now uses the index on the generated column:
SELECT student_id, first_name
FROM students
WHERE major_code = 'CS';

-- MySQL 8 shorthand with functional index on JSON path:
ALTER TABLE students
    ADD INDEX idx_json_major ((JSON_UNQUOTE(JSON_EXTRACT(pref_json, '$.major'))));

-- Find redundant indexes using sys schema
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'university';

-- Find unused indexes (zero selects since last restart)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'university';

-- Slow: full table scan + filesort
SELECT student_id, last_name, first_name, gpa
FROM students
WHERE dept_id = 4
ORDER BY gpa DESC
LIMIT 10;
-- EXPLAIN: type=ALL, Extra=Using where; Using filesort

-- Fix: composite index — equality first, then range/sort column
ALTER TABLE students
    ADD INDEX idx_dept_gpa_desc (dept_id, gpa DESC);

-- Now: type=ref, Extra=Using index condition  (no filesort!)

-- Slow join: missing index on FK side
SELECT s.first_name, s.last_name, c.title, e.grade
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c     ON e.course_id  = c.course_id
WHERE s.dept_id = 5
  AND e.semester = '2024FA';

-- Check: enrollments.student_id and enrollments.course_id indexed?
SHOW INDEX FROM enrollments;

-- Add if missing:
ALTER TABLE enrollments
    ADD INDEX idx_enr_student (student_id),
    ADD INDEX idx_enr_course  (course_id),
    ADD INDEX idx_enr_stu_sem (student_id, semester);

-- Report: grade distribution per department per semester
-- Runs every night — must be fast
SELECT
    d.dept_name,
    e.semester,
    AVG(g.points / g.max_points * 100) AS avg_score,
    COUNT(*)                             AS enrollment_count
FROM enrollments e
JOIN students  s ON e.student_id = s.student_id
JOIN departments d ON s.dept_id = d.dept_id
JOIN grades    g ON e.student_id = g.student_id
                 AND e.course_id  = g.course_id
WHERE e.semester = '2024FA'
GROUP BY d.dept_name, e.semester;

-- Covering index on grades eliminates all row lookups:
ALTER TABLE grades
    ADD INDEX idx_grades_rpt (student_id, course_id, points, max_points);

-- Covering index on enrollments:
ALTER TABLE enrollments
    ADD INDEX idx_enr_sem_stu_crs (semester, student_id, course_id);