Question :
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.
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.