Description
The backend endpoints /dashboard/history and /dashboard/stats perform queries that sort and filter by the date and user_email fields. For example, the history pagination query:
python
cursor = (
db.problem_info.find(user_filter, {"_id": 0})
.sort("date", -1)
.skip(skip)
.limit(page_size)
)
Currently, there are absolutely no indexes created in MongoDB for the problem_info or users collections during the application startup/setup. Because user_email and date are unindexed, MongoDB is forced to perform a full collection scan (COLLSCAN) and an expensive in-memory SORT for every single API call to the dashboard. As the database grows beyond a few thousand records, this will cause severe latency, increased backend CPU load, and eventually memory exhaustion (crashing the database).
Steps To Reproduce
Connect to the project's MongoDB instance using MongoDB Compass or the Mongo Shell.
Run an explain plan on the exact query the dashboard uses: db.problem_info.find({ "user_email": "test@example.com" }).sort({ "date": -1 }).explain("executionStats")
Observe the output.
Alternatively, inject 100,000 dummy records into the database for a single user and attempt to load the React dashboard; observe the multi-second latency on the /dashboard/history API call.
Expected Behavior
The explain("executionStats") output should show IXSCAN (Index Scan) as the winning plan, utilizing a compound index to instantly retrieve the paginated data in ~10 milliseconds regardless of collection size.
Environment
OS: Any
Browser: N/A (Backend Database issue)
Version: Latest main branch
Description
The backend endpoints /dashboard/history and /dashboard/stats perform queries that sort and filter by the date and user_email fields. For example, the history pagination query:
python
cursor = (
db.problem_info.find(user_filter, {"_id": 0})
.sort("date", -1)
.skip(skip)
.limit(page_size)
)
Currently, there are absolutely no indexes created in MongoDB for the problem_info or users collections during the application startup/setup. Because user_email and date are unindexed, MongoDB is forced to perform a full collection scan (COLLSCAN) and an expensive in-memory SORT for every single API call to the dashboard. As the database grows beyond a few thousand records, this will cause severe latency, increased backend CPU load, and eventually memory exhaustion (crashing the database).
Steps To Reproduce
Connect to the project's MongoDB instance using MongoDB Compass or the Mongo Shell.
Run an explain plan on the exact query the dashboard uses: db.problem_info.find({ "user_email": "test@example.com" }).sort({ "date": -1 }).explain("executionStats")
Observe the output.
Alternatively, inject 100,000 dummy records into the database for a single user and attempt to load the React dashboard; observe the multi-second latency on the /dashboard/history API call.
Expected Behavior
The explain("executionStats") output should show IXSCAN (Index Scan) as the winning plan, utilizing a compound index to instantly retrieve the paginated data in ~10 milliseconds regardless of collection size.
Environment
OS: Any
Browser: N/A (Backend Database issue)
Version: Latest main branch