Mysql – Best way to design tournament database

• Computed columns are your friend. Use them to capture simple calculations you're going to do anyway, and to guarantee that the computed values are correct.
• Persist the results if you want to index or filter on them; don't persist them if you just need to pull the value once in a while.
• Capture all data constraints using CHECK constraints.

Here is a pseudo-schema definition:

  id               PRIMARY KEY
, truck_num

, start_date
, end_date         CHECK (end_date > start_date)

, start_miles      CHECK (start_miles >= 0)
, end_miles        CHECK (end_miles > start_miles)

                   -- what if they refill the tank?
, start_gals       CHECK (start_gals >= 0)
, end_gals         CHECK (end_gals < start_gals AND end_gals >= 0)

-- all these should be computed
, miles = end_miles - start_miles
, gals = start_gals - end_gals
, mpg = miles/gals

As for indexing the table, here's what I think:

• Cluster on start_date ASC. This will satisfy your WHERE clause. You are always inserting data by increasing start_date, meaning your writes will always be sequential under this clustering scheme. You are also always querying by start_date so you satisfy your biggest query pattern as well. (3,000 inserts per week is nothing. Because you have such a low volume of inserts, you could even cluster on start_date ASC, end_date ASC.)
• Create a non-clustered index on truck_num and INCLUDE mpg. This should satisfy your SELECT, JOIN, and GROUP BY clauses. If you want to ORDER BY mpg, then make mpg part of the index key after truck_num instead of just INCLUDE-ing it.

When you're done, test your setup as follows:

1. Create a test table
2. Pump it full of test data
3. Create the indexes
4. Update statistics
5. Run your most common queries
6. Check their plans and run times

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.