gistfile1.sql

attach database "../tabular-data/comp_res.sqlite" as comp_res;

explain query plan select OGC_FID, GEOMETRY, (
    (
        cast(dem as real) / 
        cast(total as real) * 100
    ) - 
    (
        cast(replace(group_concat(dem, " "), dem, "") as real)  /
        cast(replace(group_concat(total, " "), total, "") as real) * 100
    )
) as diff
from all_counties
join comp_res.results on (all_counties.state || all_counties.county) = results.geoid
where (geoid / geoid = 1) and
    level = "CTY" and
    (
        (year = "08" and race = "P") or    
        (year = "04" and race = "P")
    ) 
group by geoid
order by geoid, year desc;

query plan

-- sqlite 3.7.5 (apple)
0|0|0|SCAN TABLE all_counties (~1000000 rows)
0|1|1|SEARCH TABLE results USING INDEX results_idx (geoid=?) (~2 rows)
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|USE TEMP B-TREE FOR ORDER BY

-- sqlite 3.7.7
0|0|0|SCAN TABLE all_counties (~1000000 rows)
0|1|1|SEARCH TABLE results USING AUTOMATIC COVERING INDEX (year=? AND race=?) (~7 rows)
0|1|1|SEARCH TABLE results USING AUTOMATIC COVERING INDEX (year=? AND race=?) (~7 rows)
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|USE TEMP B-TREE FOR ORDER BY