34 lines
1.8 KiB
MySQL
34 lines
1.8 KiB
MySQL
|
-- Migration 060: Übungslisten bei großem Bestand (Ziel: Tausende Übungen, viele Filterkombinationen).
|
||
|
|
-- Ergänzt 058 (globale Sortierung / created_by): kleinere Partial-Indizes für häufige
|
||
|
|
-- Sichtbarkeits-Pfade der Bibliothek sowie Junction-Indizes für die List-Subqueries
|
||
|
|
-- (primary_focus_name / JSON-Aggregate mit is_primary).
|
||
|
|
--
|
||
|
|
-- Bereits vorhanden und sinnvoll: UNIQUE(exercise_id, …) auf den M:N-Tabellen für EXISTS-Joins;
|
||
|
|
-- GIN auf exercises.search_vector (014); idx_exercises_exercise_kind (056).
|
||
|
|
|
||
|
|
-- Official: OR-Zweig der Bibliothek — kompakter als Full-Table-Scan bei BitmapOr mit anderen Partial-Indizes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_exercises_list_official_updated
|
||
|
|
ON exercises (updated_at DESC)
|
||
|
|
WHERE visibility = 'official'
|
||
|
|
AND COALESCE(status, '') <> 'archived';
|
||
|
|
|
||
|
|
-- Club: häufig club_id + Sortierung nach updated_at (Mandanten-Bibliothek)
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_exercises_list_club_updated
|
||
|
|
ON exercises (club_id, updated_at DESC)
|
||
|
|
WHERE visibility = 'club'
|
||
|
|
AND club_id IS NOT NULL
|
||
|
|
AND COALESCE(status, '') <> 'archived';
|
||
|
|
|
||
|
|
-- List-SELECT: Subqueries / json_agg sortieren zuerst nach is_primary (siehe exercises.py)
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_exercise_focus_areas_exercise_primary
|
||
|
|
ON exercise_focus_areas (exercise_id, is_primary DESC NULLS LAST, focus_area_id);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_exercise_style_directions_exercise_primary
|
||
|
|
ON exercise_style_directions (exercise_id, is_primary DESC NULLS LAST, style_direction_id);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_exercise_training_types_exercise_primary
|
||
|
|
ON exercise_training_types (exercise_id, is_primary DESC NULLS LAST, training_type_id);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_exercise_target_groups_exercise_primary
|
||
|
|
ON exercise_target_groups (exercise_id, is_primary DESC NULLS LAST, target_group_id);
|