On 1/31/2016 8:54 AM, Yannick Duch?ne wrote: > Another mystery to me. Given this test table: > > CREATE TABLE t (a TEXT, b TEXT, c INTEGER, PRIMARY KEY (a, b, c)) > > ??this query: > > SELECT Sum(c) FROM t GROUP BY a > > ??executes faster than any of these two: > > SELECT Sum(c) FROM t GROUP BY b > SELECT Sum(c) FROM t GROUP BY c
Imagine a phone directory book sorted by last name then first name. It's easy to find all people named "Smith, John", as well as all people with the last name of Smith. But the book's organization is of no help in finding all people with the first name of John - you have no choice but to scan the whole book. Same with a database index (whether implicitly created for a primary key, or otherwise) - it only helps when the condition involves some prefix of the list of columns. In your example, it would help with grouping (or sorting, or filtering) on (a), or (a, b), or (a, b, c). -- Igor Tandetnik
