MySQL - Please help with optimization, I am not sure how

Question

Hi

I would really appreciate it if some of you could help optimize my tables, as they currently take extremely long to execute because of the following issues:

Main table:

game {
   game_id [PRIMARY KEY]
   *team_id
   *stadium_id
   home_score
   opponent_score
   date
   tour
   half_home_score
   half_opp_score
   attendance
   referee_id
   coach_id
   captain_id
}

players (contains all players that played in the game) {
    *game_id
    *team_id
    *player_id
    position
    number
}

tries, conversions, penalties, dropgoals {
    *player_id
    *game_id
    *team_id
}

team {
    team_id [PRIMARY KEY]
    country
}

player {
    player_id [PRIMARY KEY]
    *team_id
    name
    surname
    fullname
    DOB
    POB
    school
    height
    weight
    position
}

I tend to make all *_id's of type (UNSIGNED INT[5])? I am a bit unsure if UNSIGNED is needed

All text is VARCHAR(500..200) depending on the size needed

I use DATE type where I can eg. DOB, date

' * ' - refers to foreign keys that are primary keys in other tables

One page is particularly slow, what happens is the following:

The page shows the lineup of players for the specific game so my queries are the following:

SELECT all player_id's,number,position FROM players table WHERE game_id is specific game's id getTries(player_id,game_id) from tries table . . getDropgoals(player_id,game_id) from dropgoals table

getPlayerName(player_id) from player table

Output to table the received details

<tr>
<td>tries</td>...<td>dropgoals</td><td>position</td><td>player's name</td><td>number</td></tr>

I would really appreciate it if someone could point out some visible pitfalls.

Regards

// edit

I have used the following query, but it only outputs the rows that found in the tries table, I want it to output all players found, but only count the number of tries that he scored, if no tries were scored for that player, it must still output the players details but 0 for tries scored.

I am not sure if my query is correct: SELECT ps.player_id, ps.position, ps.number, p.name, p.surname, COUNT(*) AS triesNo FROM players ps, player p, tries WHERE p.player_id=ps.player_id AND ps.game_id = '$game_id' AND ps.team_id IS NULL AND tries.player_id = ps.player_id GROUP BY ps.player_id ORDER BY ps.number

I want it to also return a player if he scored no tries, now it only returns the player if he scored a try.

Can anyone help please?

Answer 1

Unsigned will enable you to have a larger (double the size) int id column. The drawback would be that you will not be able to use negative ids (which is evil, anyway).

Answer 2

From the sounds of it you may be (a) running too many queries on a page load, or (b) not joining data on appropriate fields, if you're selecting from multiple tables at once.

For example from what you said, you appear to get running a whole set of queries to get player names, but you can merge that with your first query like so:

SELECT ps.player_id, ps.position, ps.number, p.name
FROM players ps, player p
WHERE p.player_id=ps.player_id

That query joins two tables on the player_id, and you end up with an array of players with id/position/number/name.

You may also want to look into indexes. It's a good idea to index any field(s) used in a WHERE clause (that aren't already indexed with a primary key).

As others have, said, you'll need to be more specific with what queries are running slow.