is it good to add index on column used in group by clause?

I have a query like-

select x, sum(y) from xyz where z>=100 group by x order by sum(y) desc;

will it be faster if I am going to add index on x?

Adding an index on X will improve your performance here a bit, ideally you'd want a composite key for X then Z as you want to do analysis for each X of everything where Z is over the 100 mark so for the index to be truly effective you need both.

At that point however you're building an index for a specific query so will the additional processing on insert / updates / deletes / re indexing / storage space all worth it for the one query (if its something that's going to be used over and over and in other locations then it may be, it may also be useful in different queries you already have)

Depending on the rest of your table structure and content you could have your clustered key as X,Z and that would do the job. if you've got columns a,b,c,d,e,f,g where a is an identity field that you're clustered on then this as a secondary index would help.

Ultimately however I've found the only way to know if an index is going to help, is to test it. The best will in the world can't ensure every specific index will help in the overall system only testing can do that.

StackExchange Database Administrators Q#54190, answer score: 2