search optimization

[Thomas Maxwell]

Say I have one table which records the goalscorers of every team (tbl_Goalscorers):

| userid | club | team | matchid | amount_of_goals|

| 1 | 1 | 1 | 1 | 2 |
| 3 | 1 | 2 | 1 | 4 |
| 1 | 1 | 1 | 4 | 3 |
| 7 | 4 | 2 | 8 | 1 |

So you can see from that table that userid #1 has scored in two games (matchid 1 & 4). You can also see that userid 3 scored in the same match that userid id 1 scored in, matchid 1. Now imagine that a lot more clubs use this site and this tables has tens of thousands of records. If I wanted to find the goalscorers from Team X in Club Y, I would have to look through the whole table (tens of thousands of records) to find them.

So I was wondering whether it would be faster to make a table like above, but for every club. So every club has x amount of teams with y amount of players, so they would probably have a couple of hundred records. Searching for the goalscorers of Team X in that table (tbl_Clubname_goalscorers) would be a lot faster, wouldn't it?

I don't have any idea what MySQl regards as 'many' records (thousands, tens of thousands, hundreds of thousands, millions, ...)? But I realize that having a couple hundred or thousand tables is probably also not very good. So I just wanted to see which of the two options would give me quicker results?

Thomas



Edited 1 time(s). Last edit at 08/06/2010 04:58PM by Thomas Maxwell.