From f23f7d2f0106882183929c740e4862a1939900d0 Mon Sep 17 00:00:00 2001 From: polwex Date: Thu, 29 May 2025 15:37:22 +0700 Subject: me again but it works! --- src/lib/db/indexes.sql | 26 ++++++++++++++++++++++++++ 1 file changed, 26 insertions(+) create mode 100644 src/lib/db/indexes.sql (limited to 'src/lib/db/indexes.sql') 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 -- cgit v1.2.3