blob: ffd29386979164c6f15828d6a9585b1654de40ae (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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);
|
