YES. AFAIK if SQLite detects that the rows are/can be made to be returned in GROUP BY order it can use internal variables to accumulate the group results. This is expected to be significantly faster than locating and updating a temporary BTree row for each record scanned.
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jeffrey Mattox Gesendet: Freitag, 03. März 2017 11:30 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [sqlite] Index usefulness for GROUP BY 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 ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
