Does SUM use Indexes?

[gnznroses]

Just wondering if I should index a column in a large table if I'll be using SUM on that column.

I don't think the index would be used?

[Old Pedant]

It could be.

For example if your query is simply
SELECT SUM(foo) FROM table;

But if you were doing something like
SELECT bar, SUM(foo)
FROM table
GROUP BY bar

how would the db engine know when "foo" values were related to which "bar" values?

Now, if you had a COMPOSITE KEY--of say bar and foo--then again it could do that all in the index.

Whether it actually would do so or not would depend on the level of sophistication of the DB engine. SQL Server most likely would. MySQL? Not sure.

Easy enough to find out: Create a simple test table and use EXPLAIN and see if it uses only the index(es).

[gnznroses]

oh ok, gotcha.

i'm using WHERE also, so like

select SUM(column) as total
from table
where colA = 1

i also have a query that does use Group By, and it's slow, so that was my main objective - speeding it up.
i thought that using group by is basically like using multiple Where statements and then returning the results for each. so that Group By colA is the same as doing
select SUM(column), colA from table where colA = 1
select SUM(column), colA from table where colA = 2
etc
then returning the results together.

storage engine is MyISAM, using MySQL

i had thought about EXPLAIN but it confuses me heh

[Old Pedant]

Okay, so the important field to key on for that is colA.

But a composite key (colA, column) might work better yet.

Having independent keys (sepately on colA and column) would probably *not* help.

This is the kind of thing that Explain was invented for, so it's worth trying to understand what it tells you.