0

I create a game in which players will gain levels. The basic information is in the table "char" (nickname, lvl, exp, money ...).

I want to make a ranking that will be updated on a regular basis and works in real time. I think it makes no sense to perform the "ORDER BY lvl" operation on the "char" table every time someone enters the "Ranking" tab on the site.

My plan is this:

1) Make an additional table: "ranking" in which the data will be physically sorted.

2) Create a trigger that will update the ranking every time someone reaches a new level or someone creates a new character. Does it make sense and is more efficient? If so, how can I do it? If not, how else to do it?

1

There is no such thing as "physical order" in the relational databases. Sometimes queries can produce the ordered results but that should be considered as "ordered by coincidence". There is absolutely no guarantee that you get the same ordered result each time.

If you need the ordered result the ORDER BY clause is a must.

0

It still would not make sense if the position in the ranking was the key in this table?

What if the table contains one million rows? Then, using "ORDER BY" every time someone enters the "Ranking" (on website) tab will be inefficient.

  • You'll never need the whole million rows at once. Well, sometimes you need them but that cases are rare and special enough. When you want to feed the data to the UI you need only a reasonable portion of the data like "Top 10" or "Top 500". If your table is properly indexed then SELECT * FROM chart ORDER BY rank DESC LIMIT 10 will be very fast. Fast enough not to worry about. – Kondybas Mar 3 at 1:46
  • May be you have to look at the problem from the different point of view. Think not about the player's rank but rather about the score gathered. Each player collects the points accumulated by field Score. The more points collected the higher player's rank is. Each time player collected the points you have to UPDATE user SET score = score+points WHERE id = 12345. And then you select the top-50: SELECT * FROM user ORDER BY score DESC LIMIT 50 – Kondybas Mar 3 at 2:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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