MySQL DB dynamic ranking or "static" separately saved

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 1

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.