CODEWITHSUNDEEP
sqlsql-serverdatabase
David542
David542

Reputation: 110267

Can indexes be used on aggregated calculations?

An index can be used to do something like this:

SELECT name FROM bigtable GROUP BY name

That is, it can traverse the btree or whatever index structure there is to get the values. However, does an index help at all with something like this?

SELECT name, count(1) FROM bigtable GROUP BY name

Note that for purposes of this question there won't be a filter or sort applied.

My thought is it wouldn't because a table-scan would be needed (regardless of whether there's an index in place) to "sum up" all the values. Is this correct, or am I missing something here? Are indexes ever helpful for aggregated calculations? How or how not?

Other than pre-storing the values, are there any ways to speed up the calculation of these values, or it's really just a matter of disk-speed here.

Upvotes: 0

Views: 923

Answers (1)

Thilo
Thilo

Reputation: 262554

My thought is it wouldn't because a table-scan would be needed (regardless of whether there's an index in place) to "sum up" all the values.

No, you can also just scan the index instead. It contains (in your example) an entry for each row and its name column value.

And unlike the table, the index will already be grouped/sorted by name, so that no additional sorting step is required. If you tried to do this with a full-table-scan, you would need a lot of memory or temporary space to run up the counters for each name.

That second part is what makes the big difference here (not so much that scanning the whole index is faster than scanning the whole table, which is also true of course, because it is smaller and maybe already cached).

You should be able to easily verify this by looking at the execution plan.

Note that for purposes of this question there won't be a filter or sort applied.

If you did refer to other columns in the query, the index is no longer a "covering index" and could no longer be used (by itself) to satisfy the query.

Upvotes: 2

Related Questions