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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
I dont think it will give. because some times if the "x" is computable item further then how do you handle that, no index will work here. If index available for conditions part columns then it will boost your selection.