Hi all,
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
? which executes in about the same time together, proportionally to the number
of returned rows. With `GROUP BY a`, execution times seems to be about half
than with the two formers. Adding or not adding a `WITHOUT ROWID` gives the
same. I give the number of rows, to show if the first one is faster than the
second one, that's not because it would returns less rows, on the opposite, it
returns a bit more then with grouping by `b`:
* Grouping by `a` results into 1360 rows in about 40ms +/-3;
* Grouping by `b` results into 1170 rows in about 65ms +/-5;
* Grouping by `c` results into 3154 rows in about 90ms +/-4.
If the primary key declaration is removed, timing when grouping by `b` or `c`
does not change, while timing when grouping by `a` become the same as with the
two formers.
I feel to witness this with both SQlite3 CLI and SQLiteBrowser (a detail I must
mention after another thread).
Is there any thing special with the first column of a composite primary key?
From an implementation point of view, this may makes sense, but I still prefer
to ask.
I first noticed this another way. This test was just to check on a simpler case.
Initially, I indirectly noticed this with something similar to this:
SELECT b, Sum(c) AS c
FROM
(SELECT b, Sum(c) AS c
FROM t
GROUP BY a, b)
GROUP BY b -- 60 ms on average
? being faster than this second simpler alternative, something I notice with
the test table too, just that the difference is less:
SELECT b, Sum(c) AS c
FROM t
GROUP BY b -- 65 to 70 ms on average
? although the first one seems to run more operations, and it's still the same
if I add an index on `b` for the second alternative and thus it does not use a
temporary B?tree for grouping.
I also noticed some other cases where queries executes faster on the first
column of a composite key (with or without indexes), but I won't expose all
cases, as I'm already too lengthy.
Enough testing for now, I will resume the investigations on this unexpected
results, later.
--
Yannick Duch?ne
