26 lines
1.2 KiB
SQL
Executable File
26 lines
1.2 KiB
SQL
Executable File
-- Migration: Add indexes for optimized daily attendance queries
|
|
-- Run this on your MySQL database to improve performance for large datasets
|
|
|
|
-- Index for daily attendance lookup (most common query pattern)
|
|
CREATE INDEX IF NOT EXISTS idx_attendance_date ON attendance(date_str);
|
|
|
|
-- Composite index for filtered daily queries (date + class + status)
|
|
CREATE INDEX IF NOT EXISTS idx_attendance_date_class ON attendance(date_str, class_name);
|
|
CREATE INDEX IF NOT EXISTS idx_attendance_date_status ON attendance(date_str, status);
|
|
CREATE INDEX IF NOT EXISTS idx_attendance_date_class_status ON attendance(date_str, class_name, status);
|
|
|
|
-- Index for user name search (LIKE queries)
|
|
CREATE INDEX IF NOT EXISTS idx_attendance_username ON attendance(user_name);
|
|
|
|
-- Index for pagination ordering (used with ORDER BY)
|
|
CREATE INDEX IF NOT EXISTS idx_attendance_classname_username ON attendance(class_name, user_name);
|
|
|
|
-- For faster COUNT queries
|
|
CREATE INDEX IF NOT EXISTS idx_attendance_status ON attendance(status);
|
|
|
|
-- Verify indexes were created
|
|
SHOW INDEX FROM attendance;
|
|
|
|
-- Performance tip: For very large tables (>100k rows), consider partitioning by month:
|
|
-- ALTER TABLE attendance PARTITION BY RANGE (YEAR(date_str) * 100 + MONTH(date_str));
|