1 files changed, 26 insertions, 0 deletions
diff --git a/src/lib/db/indexes.sql b/src/lib/db/indexes.sql
new file mode 100644
index 0000000..ffd2938
--- /dev/null
+++ b/src/lib/db/indexes.sql
@@ -0,0 +1,26 @@
+-- Performance-enhancing indexes for the SRS database
+
+-- User progress indexes
+CREATE INDEX IF NOT EXISTS idx_user_progress_next_review ON user_progress(user_id, next_review_date);
+CREATE INDEX IF NOT EXISTS idx_user_progress_mastered ON user_progress(user_id, is_mastered);
+
+-- Expressions related indexes
+CREATE INDEX IF NOT EXISTS idx_expressions_combined ON expressions(lang, type, frequency);
+CREATE INDEX IF NOT EXISTS idx_expressions_spelling_lang ON expressions(spelling, lang);
+
+-- Cards and lessons indexes
+CREATE INDEX IF NOT EXISTS idx_cards_lessons_lesson ON cards_lessons(lesson_id);
+CREATE INDEX IF NOT EXISTS idx_cards_lessons_card ON cards_lessons(card_id);
+CREATE INDEX IF NOT EXISTS idx_cards_expressions_expression ON cards_expressions(expression_id);
+
+-- Bookmarks index
+CREATE INDEX IF NOT EXISTS idx_bookmarks_user ON bookmarks(user_id);
+
+-- Lessons indexes
+CREATE INDEX IF NOT EXISTS idx_lang_lessons_lang ON lang_lessons(lang);
+
+-- Attempts indexes
+CREATE INDEX IF NOT EXISTS idx_attempts_timestamp ON attempts(timestamp);
+
+-- Combined index for senses parent lookup
+CREATE INDEX IF NOT EXISTS idx_senses_parent_combined ON senses(parent_id, pos);
+\ No newline at end of file