BUG: /folders/all-folders takes over a second when folders contain both images and videos

Open Beginner friendly
  • Is something wrong?
#1,708 3 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 6 days

@Saurabhyadav0 is already working on this.

Since Nov 13, 2026.

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
75/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
python, sqlite
Domain
backend, databases

Research direction

The slow query is in db_get_all_folder_details() in backend/app/database/folders.py, which left-joins images and videos onto folders and counts with COUNT(DISTINCT). Read the three comments first to check whether someone has already claimed it. Rewrite it to count each table in its own GROUP BY folder_id subquery and join those totals onto folders. Done when the response for /folders/all-folders is unchanged for empty, image-only, video-only and mixed folders, and a new test in the existing folder tests asserts those exact counts.

Written by the indexing model from the issue text.

Description

bug enhancement possible-duplicate
Is there an existing issue for this?
  • I have searched the existing issues
What happened?

db_get_all_folder_details() in backend/app/database/folders.py left-joins both images and videos onto folders on the same folder_id and uses COUNT(DISTINCT ...) to keep the counts right. The counts are correct, but SQLite still builds images × videos rows per folder before counting, so the cost grows with the product rather than the sum.

FROM folders f
LEFT JOIN images i ON f.folder_id = i.folder_id
LEFT JOIN videos v ON f.folder_id = v.folder_id
GROUP BY f.folder_id

While the folder list in Settings is open and a folder with AI tagging is still indexing, the frontend polls this endpoint every second (frontend/src/hooks/useFolderOperations.tsx), so on a large mixed library each poll takes over a second.

GET /folders/all-folders, median of 5, on a database built with the project's db_create_* functions:

Library Now
20 folders × 2,500 images, no videos 66 ms
20 folders × 2,500 images × 10 videos 144 ms
20 folders × 2,500 images × 100 videos 1,192 ms
1 folder × 10,000 images × 500 videos 1,184 ms

Counting each table in its own GROUP BY folder_id subquery and joining the totals onto folders returns a byte-identical response in 3–20 ms for all of the above (also checked against empty, image-only and video-only folders). An index on folder_id alone doesn't help, since the row multiplication is the cost — with indexes added the current query actually got slower.

The query plan confirms the multiplication:

SCAN f ... | SEARCH i ... LEFT-JOIN | SEARCH v ... LEFT-JOIN
| USE TEMP B-TREE FOR count(DISTINCT) | USE TEMP B-TREE FOR count(DISTINCT)

Related: #876 reports the Settings folder list being slow in general. It predates this join (added in fab7ef08, 2026-07-24), so it isn't caused by it, but fixing this removes one source of that slowness. #1404 point 1 is what added video_count (the second join) — the counts it asked for are correct; this is about the cost only.

I'd like to work on this: rewrite the query to count each table in its own grouped subquery, and add a test with a folder holding several images and several videos (plus empty / image-only / video-only) asserting the exact counts are unchanged.

Record
  • I agree to follow this project's Code of Conduct
Dominant language
Python
Stars
313
Forks
755
Avg merge
6d 36m
Merged PRs (30d)
8

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from AOSSIE-Org/PictoPy

All issues in AOSSIE-Org/PictoPy

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.