0

I'm currently developing an application which saves the progression of a player during an online game. Which means, I want to store for example his score, played time, Flag Captures etc. All in all there are 15-20 columns for different types of data, most of them are integers.

Now I want to sort each column to create statistics for player-ranking, for example:
SELECT players.UserName AS Name, Score FROM player_data JOIN players USING(UserID) ORDER BY Score DESC LIMIT 1, 20.
It should not be limited to query only "Score", every other existing columns should be queried in this way.

Is there any other way except to create indexes on all columns to speed up such queries? Or are there any other ways for optimization? I'm fairly new to databases but as far as I read, creating many indexes will slow down update and insert-statements, as well as increasing the database size. Obviously I want small database size and fast queries.

0

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.)

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.