Index a sum column

Question

Is creating an index for a column that is being summed is faster than no index?

Answer 1

Sorry, it is not clear what you are asking.

Are you asking, would it speed up a query such as

SELECT product, sum(quantity) FROM receipts  GROUP BY product 

if you added an index on quantity?

If that is the question, then the answer is no. Generally speaking, indexes are helpful when you need to find just a few rows among many; here you need all rows, so an index does not help.

There is an obscure exception (which applies so rarely most DB optimizers probably don't bother implementing this trick). If your query happens to be

SELECT sum(foo) FROM bar 

, where there is an index on foo, and bar is a table with many columns, it is possible to read in the full index, incurring a smaller hit than if you read the underlying table, and get the answer directly from the index -- never having to touch the "real" table at all! This is a fairly rare case, however, and you will want to test that your optimizer knows to do this before relying on this too much.