Likely the majority of the performance issue you're seeing is just the normal constraints of the data limitations of SQLite. Your query is of sound mind, and I don't believe there's much you can do to optimize it other than re-write the
WHERE predicate to something more efficiently relational with an
INNER JOIN like this:
WITH file_id_counts AS (
SELECT hashband, COUNT(DISTINCT(file_id)) as count
GROUP BY hashband
HAVING COUNT > 1
) SELECT hashband, file_id, window_id
INNER JOIN file_id_counts
ON hashbands.hashband = file_id_counts.hashband
ORDER BY hashband
This is logically equivalent and the reasoning it could be faster is because the
IN clause is syntactical sugar for a bunch of
OR clauses which can be less efficient than a direct single equality operator like the
INNER JOIN is doing above.
Additionally, ensuring your
hashbands table has an index on at least
(hashband) or possibly on
(hashband, file_id) should help (if one doesn't exist already).
Finally, if possible, removing your
ORDER BY clause, and instead doing the sorting in your consuming application would probably help a small amount as well. While this mostly just moves the onus of the sort to a different part of the call stack, generally sorting in the database can add a little extra complexity that sometimes can be resolved and slightly more efficient to do in the consuming application. Plus, sorting is really presentation logic in my opinion (at least when not used for a functional purpose of the query).
Where does this SQLite database live?...a mobile application, or on its own server?