SQLite Forum

big performance hit when querying 2 values in a single query instead of two

For some reason the optimizer chooses to use a separate b-tree for distinct even though there is already an index.  It does this even for the base query `select count(distinct id) from pro_comment` sometimes, even though scanning the existing index would be more efficient (lower cost).  

Generating the separate b-tree requires an additional scan for no benefit.

I presume that this is because the optimizer is not considering the more direct solution of merely doing a single scan of the index to generate the result.