Indexing on frequently updated table

I was asked a question in an interview, where I was given a situation which is:
I have registered to an online gaming website. It displays my rank on the basis of my score when I play a game.
Database table has 2 columns: 1. UserName 2. Score.
Since the login page was loading very slowly so the programmers created an non-clustered index on UserName and now the welcome page problem is solved. But since millions of users are playing games , their scores get updated quite frequently. So the user rank calculation is performed quite slow. Now another non-clustered index is created on Score but its getting frequently updated so the performance is not improved.
How to come out of this dilemma?

Questions by mahmoodbutt

Showing Answers 1 - 9 of 9 Answers

Bhanu

  • Sep 1st, 2014
 

Why don't you create both non clustered indexes in one non clustered index.Please let me know on your performance.

  Was this answer useful?  Yes

nabin

  • Mar 25th, 2015
 

create clustered index on name. Reason is clustered index should be applied on columns which do not change frequently. Since the user will register once and will not change name that frequently it is good to apply on name. Since the question does not hint on if the table has any other primary key, we can put an clustered index on name. Usually we should look to apply clustered index on columns which are unique, do not change frequently. Non-clustered index are generally applied on columns which appear on WHERE, JOIN clauses.

  Was this answer useful?  Yes

Yaman

  • Aug 9th, 2015
 

First of all table is not well designed. Table should have a clustered index. Right now its a heap and if we create a clustered index on username its a bad idea as data gets physically sorted everytime the row is inserted and exclusive lock is held on a table. I would suggest creating a covering non clustered index which will have an index key on username and score in the included part of the index. 

Code
  1. CREATE INDEX IDX_Username_Incl_Score ON Users(Username) Include(Score)

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions