Week 7 — Query Execution Plans & Advanced Optimization¶

-- Basic EXPLAIN
EXPLAIN
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'
ORDER BY s.last_name;

EXPLAIN FORMAT=JSON
SELECT s.last_name, COUNT(e.course_id) AS courses
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
WHERE s.dept_id = 3
GROUP BY s.student_id, s.last_name\G

EXPLAIN ANALYZE
SELECT s.last_name, AVG(g.points) AS avg_pts
FROM students s
JOIN grades g ON s.student_id = g.student_id
WHERE s.dept_id = 5
GROUP BY s.student_id, s.last_name\G

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT 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';

-- USE INDEX: restrict candidate indexes
SELECT * FROM students USE INDEX (idx_dept_gpa)
WHERE dept_id = 3 AND gpa > 3.5;

-- FORCE INDEX: use this index even if optimizer thinks table scan is cheaper
SELECT * FROM enrollments FORCE INDEX (idx_enr_stu_sem)
WHERE student_id = 1001 AND semester = '2024FA';

-- IGNORE INDEX: exclude a specific index
SELECT * FROM students IGNORE INDEX (idx_lastname)
WHERE last_name = 'Chen';

-- STRAIGHT_JOIN: force table join order exactly as written
SELECT STRAIGHT_JOIN s.*, e.*
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE s.dept_id = 3;

-- NO_MERGE hint (MySQL 8): prevent derived table from being merged
SELECT /*+ NO_MERGE(dept_avg) */ s.first_name, dept_avg.avg_gpa
FROM students s
JOIN (SELECT dept_id, AVG(gpa) AS avg_gpa
      FROM students GROUP BY dept_id) dept_avg
  ON s.dept_id = dept_avg.dept_id;

-- See all optimizer switches
SHOW VARIABLES LIKE 'optimizer_switch'\G

-- Common switches to tune
SET SESSION optimizer_switch =
    'block_nested_loop=off,'    -- disable BNL; prefer hash join (MySQL 8)
    'batched_key_access=on,'    -- enable BKA for index-based joins
    'mrr=on,'                   -- Multi-Range Read: random → sequential I/O
    'mrr_cost_based=on';        -- Let optimizer decide when to use MRR

-- Disable hash join for a session (MySQL 8 default: on)
SET SESSION optimizer_switch = 'hash_join=off';

-- Enable and configure (MySQL)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1.0;           -- seconds; 0 logs EVERYTHING
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL log_slow_verbosity = 'query_plan,innodb';  -- Percona/MariaDB

-- Verify
SHOW VARIABLES LIKE 'slow%';
SHOW VARIABLES LIKE 'long_query_time';

-- ❌ ANTI-PATTERN: function prevents index use
SELECT * FROM students WHERE YEAR(enrolled_on) = 2023;
SELECT * FROM students WHERE UPPER(last_name)  = 'CHEN';
SELECT * FROM enrollments WHERE DATE(created_at) = '2024-11-01';

-- ✅ REWRITE: transform predicate to range or use functional index
SELECT * FROM students
WHERE enrolled_on >= '2023-01-01' AND enrolled_on < '2024-01-01';

SELECT * FROM students WHERE last_name = 'Chen';  -- rely on collation for case

SELECT * FROM enrollments
WHERE created_at >= '2024-11-01 00:00:00'
  AND created_at <  '2024-11-02 00:00:00';

-- student_id is INT; passing string causes implicit CAST
-- ❌ ANTI-PATTERN
SELECT * FROM students WHERE student_id = '1001';
-- MySQL silently converts '1001' → 1001, but:

-- ❌ Worse: indexed VARCHAR column compared to integer
-- index on email (VARCHAR); comparing to integer loses index
SELECT * FROM students WHERE email = 12345;
-- MySQL converts ALL email values to numbers: full table scan!

-- ✅ Always match data types exactly
SELECT * FROM students WHERE email = 'jsmith@frostburg.edu';

-- ❌ ANTI-PATTERN: SELECT * prevents covering indexes, wastes bandwidth
SELECT * FROM students WHERE dept_id = 3;

-- ✅ Select only needed columns — enables covering index opportunities
SELECT student_id, first_name, last_name, gpa
FROM students WHERE dept_id = 3;

-- ❌ Cannot use B-tree index — must scan all index leaves
SELECT * FROM courses WHERE title LIKE '%database%';

-- ✅ Option 1: trailing wildcard only — uses index
SELECT * FROM courses WHERE title LIKE 'Database%';

-- ✅ Option 2: FULLTEXT index for contains-style search
SELECT * FROM courses
WHERE MATCH(title, description) AGAINST ('database' IN BOOLEAN MODE);

-- ❌ OR across different indexed columns often degrades to table scan
SELECT * FROM students WHERE dept_id = 3 OR last_name = 'Smith';

-- ✅ UNION rewrites each branch to use its own index
SELECT * FROM students WHERE dept_id = 3
UNION ALL
SELECT * FROM students WHERE last_name = 'Smith' AND dept_id <> 3;

-- ❌ IN with thousands of values: optimizer may give up
SELECT * FROM enrollments WHERE student_id IN (1,2,3,...,50000);

-- ✅ Use a JOIN or temporary table instead
CREATE TEMPORARY TABLE target_students (student_id INT PRIMARY KEY);
INSERT INTO target_students VALUES (1),(2),(3),...;
SELECT e.* FROM enrollments e JOIN target_students t USING (student_id);

-- ❌ IN with correlated subquery: may execute subquery once per outer row
SELECT * FROM students
WHERE student_id IN (
    SELECT student_id FROM enrollments WHERE semester = '2024FA'
);

-- ✅ EXISTS short-circuits on first match (especially with index on inner column)
SELECT * FROM students s
WHERE EXISTS (
    SELECT 1 FROM enrollments e
    WHERE e.student_id = s.student_id AND e.semester = '2024FA'
);

-- ✅ Even better: semi-join rewrite (optimizer may do this automatically in MySQL 8)
SELECT DISTINCT s.*
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.semester = '2024FA';

-- InnoDB auto-recalculates when 10% of rows change (innodb_stats_auto_recalc = ON)
-- For tables with >1M rows, 10% threshold means stats can be stale for a long time

-- Increase sample pages for accuracy (global setting)
SET GLOBAL innodb_stats_persistent_sample_pages = 50;  -- default: 20

-- Manual recalculation for critical tables
ANALYZE TABLE students, courses, enrollments, grades;

-- Check the raw statistics tables
SELECT * FROM mysql.innodb_table_stats WHERE database_name = 'university';
SELECT * FROM mysql.innodb_index_stats
WHERE database_name = 'university'
  AND table_name = 'enrollments'\G

-- See current size and hit rate
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

SELECT
    ROUND(TP.variable_value / 1024 / 1024, 0) AS pool_mb,
    ROUND(HR.variable_value / (HR.variable_value + RD.variable_value) * 100, 2) AS hit_rate_pct
FROM performance_schema.global_status TP
JOIN performance_schema.global_status HR ON HR.variable_name = 'Innodb_buffer_pool_read_requests'
JOIN performance_schema.global_status RD ON RD.variable_name = 'Innodb_buffer_pool_reads'
WHERE TP.variable_name = 'Innodb_buffer_pool_pages_total';

-- Sort buffer: allocated per sort operation per thread
SET GLOBAL sort_buffer_size = 4 * 1024 * 1024;   -- 4 MB default; 8–16 MB for heavy sorts

-- Join buffer: used when join has no index (nested loop + buffer)
SET GLOBAL join_buffer_size = 8 * 1024 * 1024;   -- 256 KB default; increase for hash joins

-- Read buffer for sequential scans
SET GLOBAL read_buffer_size = 2 * 1024 * 1024;   -- 128 KB default

-- Check memory usage summary
SELECT event_name, current_alloc, high_alloc
FROM sys.memory_global_by_current_bytes
ORDER BY current_alloc DESC
LIMIT 20;

-- Top 10 slowest statements since server start
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e9 AS avg_sec,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- Tables with most full table scans
SELECT OBJECT_NAME, COUNT_READ, COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_table
WHERE COUNT_FETCH > 0
ORDER BY COUNT_FETCH DESC
LIMIT 10;

-- Index usage statistics
SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'university'
ORDER BY COUNT_FETCH DESC;