On 4/24/2013 11:49 AM, Larry Brasfield wrote:
*Daniel Winter wrote:0*
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.
Note though that the query doesn't have an ORDER BY clause. It doesn't
request rows in any particular order. SQLite could, in principle,
reorder columns in GROUP BY to take advantage of the index. I suppose
the optimizer just happens to miss this particular opportunity.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
