*Daniel Winter wrote:0* > I discovered that the order of columns in a group by affects the > performance of a query. Is this expected?
Yes. > For example: > > Table: Column A int, Column B int, Column C int > One Index: A,B (combined) > > Query 1: SELECT A,B,count(*) from tableTest group by A,B > Query 2: SELECT A,B,count(*) from tableTest group by B,A > > Query 1 will use the index, while query 2 will not. (which makes Query 1 a > lot faster with bigger tables). Both querys will result with the same data. > I do not really understand why it doesn't use the index for both querys. The index has row references pre-sorted according to the criteria specified for it. That is why it can be used to speed up a query which sorts by those criteria. There is no mystery as to why it does not help to speed up other queries which do not sort by those criteria. Looking at the query plan would show the index used for your first query and not for the second. A similar effect would be seen with joins which are typically processed as a sifted merge of sorted components. Best regards, -- Larry _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
