Take the 2-minute tour ×
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's 100% free, no registration required.

I have a table where I store the points with the connected categories and game-types of each user. There are different ways to get points. However everytime when the points need to get displayed in a ranking I get them in an SELECT statement, where I group and add them up.

I was wondering if it is more efficient to store and update the points of a user in a table everytime the points change. (Since I need the individual points and game-stats aswell I would still store them in the table I mentioned above.) But the SELECT statement would be simpler and less complicated. However I think the way I am doing this is the favorable one but since I did not find any post about that I wanted to seek opinion of others.

I hope this is understandable.

Thank you.

share|improve this question
add comment

1 Answer

The dynamic rank would seem more fluid.

As long as you index the rank field, updating the points gives the rank index a workout and gets the rank dynamically sorted in a logical manner at the same time.

The execution of the query would be

  • query parsing
  • query optimization
  • rank index scan (if rank is indexed)
  • limit on result set determined by value of dynamic variable

Updating points and then running a single select would execute the query like this

  • query parsing
  • query optimization
  • points index scan (if points is indexed)
  • limit on result set determined by the compiled query

Sorting ranks vs sorting points could be just a matter of preference.

However, there is something to consider. If you rank by top 10,000 or 20,000, rank should be a SMALLINT. If the points is also SMALLINT (< 65536 or 64K), then either index/query scenario is fine. However, if points can go beyond 64K and need INT, then go with indexing the rank and dynamically assign the rank.

I would also store rank and point together in the same table to avoid the need to do JOINs. It already has a full plate doing sorts via the index on the points/rank.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.