26

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

Nikhil Sahu
  • 2,463
  • 2
  • 32
  • 48
user26087
  • 809
  • 2
  • 13
  • 21

5 Answers5

48

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.

SquareCog
  • 19,421
  • 8
  • 49
  • 63
  • 1
    +1 Good advice: to view the execution plan produced by the optimizer. – Amy B Jan 13 '09 at 04:24
  • Is this always true - that indexes can't affect SUM performance? What if we are using filter index where indicating that the value IS NOT NULL? And when we are using WHERE clause to SUM only particular values, would a index help? – gotqn Nov 15 '12 at 09:22
  • 1
    I am using Mysql 5.7 with innodb and the query plan explains that for sum of columns, optimizer does not looks beyond the covering index. – Nikhil Sahu Jul 20 '17 at 06:31
  • 1
    The optimisation is not that obscure. Mysql and postgres will scan the index only if you don't need values from outside it. – viraptor Aug 30 '18 at 08:30
12

No. Indexes improve searches by limiting how many checks are required. An aggregate function (count, max, min, sum, avg) has to run through all the entries in a column regardless.

Spencer Ruport
  • 34,865
  • 12
  • 85
  • 147
  • 4
    But if all those columns are present in the index itself, the actual table need not be accessed, making the sum faster than with the case with no index – Nikhil Sahu Jul 20 '17 at 06:29
5

If you want to make the summation faster, you can pre-materialized the result. On Oracle, use Materialized Views, on MS SQL use Indexed Views.

On your specific question "Is creating an index for a column that is being summed is faster than no index?", answer is No.

The answer to your question lies on Spencer's answer:

"An aggregate function (count, max, min, sum, avg) has to run through all the entries in a columns being summed regardless."

Just clarified the context of columns in Spencer's answer. His answer is correct nonetheless.

Michael Buen
  • 38,643
  • 9
  • 94
  • 118
0

If the index is covering, it will generally be faster. How much faster will be determined by the difference between the number of columns in the table versus the number in the index. In addition, it might be faster if there are any filtering criteria.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
0

I found indexing a column in the where(productid here) helps when using this query:

SELECT productid, sum(quantity) FROM receipts WHERE productid = 1 GROUP BY productid

One of my queries went from 45 seconds to almost instant once I added the index.

  • 1
    With a single product ID, do you need the product ID in the SELECT list? – Jonathan Leffler May 19 '09 at 22:29
  • 3
    yeah but as SquareCog said, adding an index to productid helps because you are finding rows based on productid. The question, in your case, is whether adding an index on quantity would help – alex9311 Jun 14 '16 at 08:08