Given this DB schema (simplified, there are other columns): CREATE TABLE History ( history_ID INTEGER PRIMARY KEY, gameCount INTEGER, weekday INTEGER, /* 0=Sunday, 6=Saturday */ hour INTEGER, /* (0..23) */ datetime INTEGER /* unix datetime */ );
CREATE INDEX Idx_weekday ON History( weekday ); ------------- Now, I look at a recent set of rows... SELECT TOTAL(gameCount), weekday FROM History WHERE datetime >= strftime('%s','now','-28 days') GROUP BY weekday ORDER BY 1 DESC QUERY PLANS: without the index: 0 0 0 SCAN TABLE History 0 0 0 USE TEMP B-TREE FOR GROUP BY <-- weekday (7 groups) 0 0 0 USE TEMP B-TREE FOR ORDER BY with the index: 0 0 0 SCAN TABLE History USING INDEX Idx_weekday 0 0 0 USE TEMP B-TREE FOR ORDER BY Either way, the entire table is scanned (right?). My index covers the entire table, but the TEMP B-TREE FOR GROUP BY contains only the rows matching the WHERE clause, so the TEMP B-TREE is much smaller (right?). So, is my index on weekday worthwhile, time-wise and space-wise? (Query speed is not a big issue for me, and the DB is relatively small -- there are, at most, 60 rows added per day. Memory is plentiful, OSX). --- Jeff _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users