Mysql – What to do if all columns should be queried

indexMySQL

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.

Best Answer

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

Related Solutions

Mysql – How to design tables for players in a tournament

UPDATED version based on feedback:

CREATE TABLE group (
    group_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY(team_id)
);

CREATE TABLE player (
    player_id INT NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    group_id INT,
    player_rank INT,
    PRIMARY KEY(player_id)
);

CREATE TABLE game (
    game_id INT NOT NULL AUTO_INCREMENT,
    match_date DATE,
    overtime TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (game_id)
);

CREATE TABLE player_game (
    game_id INT NOT NULL,
    player_id INT NOT NULL,
    player_score INT,
    PRIMARY KEY (game_id, player_id)
);

...and UPDATED explanation: I think group and player are self-explanatory. The table game will have one record per match. The player_game table records the players in a match and the results; each match will have two records, one for each player.

Since you appear to be using InnoDB as the storage engine you may want to use foreign keys to enforce referential integrity, and obviously you can add other constraints and indexes as needed. If you need some functionality that this doesn't seem to cover, ask and I will either explain or add it.

Mysql – Relational Database tables

Given that a game can only ever have two players, I suggest the following design:

create table PLAYER
(
  playerid   INTEGER PRIMARY KEY,
  -- add your other columns here
);

create table GAME
(
  gameid     INTEGER PRIMARY KEY,
  gamedate   DATETIME,
  player1id  INTEGER,
  player2id  INTEGER,
  -- add your other columns here
);

If you required a differing number of players per game, then I would have designed it differently - with your requirements there is no need to add an extra table & increase the query complexity.

Also note that I have made gameid the PRIMARY KEY. Using a DATETIME as the PK is generally not a good idea - what would happen if two games were created simultaneously?

You'll also probably want to add foreign keys to/from GAME.player1id/player2id and PLAYER.playerid.

Best Answer

Related Solutions

Mysql – How to design tables for players in a tournament

Mysql – Relational Database tables

Related Question