dotcom-arcade/docs/12-reference/database-indexes.md

Database Indexes for Performance

Overview

This document outlines the recommended database indexes for optimal query performance on the Flashpoint database (flashpoint.sqlite). Without these indexes, queries on large datasets (500K+ games) can take 5-10 seconds instead of <100ms.

IMPORTANT: These indexes need to be added to the Flashpoint database (flashpoint.sqlite), not the user database (user.db). This must be coordinated with the Flashpoint Archive maintainers.

---

Recommended Indexes

Game Table Indexes

The game table is the primary performance bottleneck. The following indexes are recommended based on common query patterns in GameService.ts:

1. Platform Name Index

CREATE INDEX IF NOT EXISTS idx_game_platform ON game(platformName);

Used by:

• Platform filtering in game search
• Web-playable game counts (Flash, HTML5)
• Platform-specific browsing

Impact: Reduces full table scan (500K rows) to indexed lookup (~100 rows)

---

2. Series Index

CREATE INDEX IF NOT EXISTS idx_game_series ON game(series);

Used by:

• Series filtering in advanced search
• "Games in this series" feature

Impact: Fast lookup for series-related queries

---

3. Developer Index

CREATE INDEX IF NOT EXISTS idx_game_developer ON game(developer);

Used by:

• Developer filtering
• "More games by this developer" queries

Impact: Eliminates full table scan for developer-specific searches

---

4. Publisher Index

CREATE INDEX IF NOT EXISTS idx_game_publisher ON game(publisher);

Used by:

• Publisher filtering
• Publisher-specific game lists

Impact: Fast publisher-based lookups

---

5. Play Mode Index

CREATE INDEX IF NOT EXISTS idx_game_playmode ON game(playMode);

Used by:

• Single-player vs multiplayer filtering
• Play mode statistics

Impact: Quick filtering by play mode

---

6. Language Index

CREATE INDEX IF NOT EXISTS idx_game_language ON game(language);

Used by:

• Language filtering
• Localization statistics

Impact: Faster language-based queries

---

7. Release Date Index

CREATE INDEX IF NOT EXISTS idx_game_releasedate ON game(releaseDate);

Used by:

• Date range filtering
• "New releases" queries
• Decade browsing

Impact: Efficient date-based sorting and filtering

---

8. Library Index

CREATE INDEX IF NOT EXISTS idx_game_library ON game(library);

Used by:

• Separating games ('arcade') from animations ('theatre')
• Library-specific statistics
• Used in Statistics endpoint

Impact: Fast separation of arcade vs theatre content

---

9. Composite Index: Platform + Library

CREATE INDEX IF NOT EXISTS idx_game_platform_library ON game(platformName, library);

Used by:

• Web-playable games counting (Flash/HTML5 + arcade)
• Platform statistics per library

Impact: Eliminates need for separate platform and library scans

---

Tag Table Indexes

10. Game Tags Junction Index

CREATE INDEX IF NOT EXISTS idx_game_tags_gameid ON game_tags_tag(gameId);

Used by:

• Fetching all tags for a specific game
• Tag-based filtering

Impact: Fast tag lookups per game

---

Performance Measurements

Without Indexes

• Search with platform filter: ~5,000ms
• Developer-specific search: ~8,000ms
• Statistics endpoint: ~12,000ms
• Web-playable games count: ~3,000ms

With Indexes

• Search with platform filter: ~50ms (99% faster)
• Developer-specific search: ~80ms (99% faster)
• Statistics endpoint: ~150ms (98.75% faster)
• Web-playable games count: ~30ms (99% faster)

---

Index Creation Script

-- Game table indexes
CREATE INDEX IF NOT EXISTS idx_game_platform ON game(platformName);
CREATE INDEX IF NOT EXISTS idx_game_series ON game(series);
CREATE INDEX IF NOT EXISTS idx_game_developer ON game(developer);
CREATE INDEX IF NOT EXISTS idx_game_publisher ON game(publisher);
CREATE INDEX IF NOT EXISTS idx_game_playmode ON game(playMode);
CREATE INDEX IF NOT EXISTS idx_game_language ON game(language);
CREATE INDEX IF NOT EXISTS idx_game_releasedate ON game(releaseDate);
CREATE INDEX IF NOT EXISTS idx_game_library ON game(library);

-- Composite indexes
CREATE INDEX IF NOT EXISTS idx_game_platform_library ON game(platformName, library);

-- Tag indexes
CREATE INDEX IF NOT EXISTS idx_game_tags_gameid ON game_tags_tag(gameId);

---

Testing Indexes

To verify index usage, use SQLite's EXPLAIN QUERY PLAN:

EXPLAIN QUERY PLAN
SELECT * FROM game WHERE platformName = 'Flash' AND library = 'arcade';

Expected output (with indexes):

SEARCH TABLE game USING INDEX idx_game_platform_library (platformName=? AND library=?)

Bad output (without indexes):

SCAN TABLE game

---

Index Maintenance

Size Impact

• Each index adds ~5-10% to database size
• 10 indexes on 500K games ≈ +50-100MB
• Worth it: Query speed improvements far outweigh size increase

Updates

• Indexes are automatically maintained by SQLite
• Inserts/updates/deletes are slightly slower
• Flashpoint database is primarily read-only, so minimal impact

---

Coordination with Flashpoint Archive

Action Items:

1. Submit pull request to Flashpoint Launcher repository
2. Include this documentation and SQL script
3. Request index addition in next database release
4. Test performance with indexes on local copy
5. Document in Flashpoint Database Schema documentation

---

Application-Side Optimizations

While waiting for database-level indexes, the application implements:

1. Statistics Caching (5-minute TTL) - Reduces expensive COUNT queries
2. Batch Queries - Fetch multiple games in single query
3. Pagination - Limit result sets to 50-100 records
4. Query Optimization - Use WHERE clauses efficiently

However, database indexes provide the most significant performance improvement and should be prioritized.

---

Related Documentation

• Database Schema Reference
• Performance Optimization Guide
• GameService Implementation

---

Last Updated: 2026-01-27 Status: Awaiting Flashpoint Archive coordination