Filtering the grid by one person under "All of them" took 38 s on the
reference library (24k images, 19k faces): a click on Catherine, 775
photographs, spent 13 s counting, 20 s drawing the timeline and 4.4 s
reading the window. The SQL took 10 ms when tried by hand, because the
hand-written version used the "Any" term.
The "All" term was a correlated COUNT(DISTINCT person_id) per image. With
one person in the IN list SQLite drove that subquery from
face_person_person, so every image walked every one of the person's
faces and opened each to read its image_id: 24,000 x 775 probes, in each
of the five queries a reload runs. Two people happened to plan from the
image side and stayed fast, which is why only the single-person case
crawled.
Both modes now list the people's photographs once from face_person, the
small side, and test the image's key against that list: GROUP BY image
with HAVING COUNT(DISTINCT person_id) = n for "All", the plain list for
"Any". On a copy of the reference catalog the grid window goes from
11.4 s to 23 ms and the count from 21.4 s to 1.8 ms. The eyes-open term
rides inside the same list, unchanged.
A test now checks the plan, not just the answer: no person or eyes
term may be correlated.
dtourolle
36e8360db9