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
