I don?t think I can always run an analyze on the TEMP table for each query. May ruin performance worse than trying my luck with the temp table.
I think this boils down why a JOIN with a 500 row table (one column) is so much (several hundred times) slower than using an IN clause with 500 values. I have not changed the code or query for a long time, so my assumption is that a change in one of the recent SQLite updates caused the changed behavior of the optimizer/query engine. I recall a similar problem maybe a year ago. I think only one of the SQLite developers may shed some light on this. For now I have increased the threshold for IN clauses (instead of TEMP tables) and use WHERE IN (SELECT ? from TEMP) instead of a JOIN.
