Mysql – Sorting ranking by trigger

MySQLsortingtrigger

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?

Best Answer

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.

Related Solutions

Mysql – What to do if all columns should be queried

If there are only a thousand rows, the query will run fast enough without a suitable index.

If you have a million rows, you really need an index.

Also, this might work faster in some situations:

SELECT u.UserName AS Name,
       d.Score
    FROM ( SELECT UserID, Score 
               FROM PlayerData
               ORDER BY Score DESC
               LIMIT 20
         ) AS d
    JOIN Players AS u  USING (UserID);

Or...

SELECT ( SELECT UserName FROM players WHERE UserID = d.UserID ) AS Name,
       Score 
    FROM PlayerData
    ORDER BY Score DESC
    LIMIT 20

(I removed the OFFSET 1 in the presumption that you don't want to skip the highest score.)

SQL Server Trigger – How to Track Changes with Triggers

I also considered a trace, but it can be easly stopped or it will not restart at server start.

You can have a server side trace as a SQL Agent Job and set up the job to start whenever SQL agent start and a second schedule to start every xx number of minute. That way job will always start and second schedule will take care if somebody stop it by mistake.

Is the trigger the wrong way? Can I fix this in some way?

I suggest you create a database role and make all the users member of that role. I understand your concern of forgetting to add users to the role or assign permission. I personally do not prefer to have too many users with privilege to change schema in my database so the list should be small and a role will make it easy for you to manage the privilege.

Best Answer

Related Solutions

Mysql – What to do if all columns should be queried

SQL Server Trigger – How to Track Changes with Triggers

Related Question