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

Aperta Adatta ai principianti
  • Qualcosa non va?
#1,708 3 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 6 giorni

@Saurabhyadav0 ci sta già lavorando.

Dal 13/11/2026.

Valutazione

Difficoltà
2/5
Tempo stimato
1-3 ore
Idoneità per principianti
75/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
python, sqlite
Ambito
backend, databases

Direzione di ricerca

La query lenta si trova in db_get_all_folder_details() in backend/app/database/folders.py, che esegue un LEFT JOIN di images e videos su folders e conta con COUNT(DISTINCT). Leggi prima i tre commenti per verificare se qualcuno l'ha già rivendicata. Riscrivila in modo che conti ogni tabella in una propria subquery GROUP BY folder_id e unisca questi totali a folders. È completata quando la risposta di /folders/all-folders resta invariata per cartelle vuote, solo immagini, solo video e miste, e un nuovo test nei test delle cartelle esistenti verifica esattamente questi conteggi.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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
Lingua principale
Python
Stelle
313
Fork
755
Merge medio
6g 36m
PR unite (30g)
8

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di AOSSIE-Org/PictoPy

Tutte le issue di AOSSIE-Org/PictoPy

Issue simili

Altre issue su Python

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.