I am creating a database that will hold all the scores for all the games a system provides.
Now this could get very big as each game could generate 1000s of scores as players play, and there will hopefully be many games in the future.
So I think I want indexis and a sutable Select statement.
Can anyone help with the design for this table and select statement, I have the following so far:

CREATE TABLE score (
id int AUTO_INCREMENT PRIMARY KEY,
gameid integer NOT NULL REFERENCES game (id),
playerid integer NOT NULL REFERENCES player (id),
score integer NOT NULL,
time time
);

I am looking at a select like thie following, in pseudoSQL:
SELECT playerid, score FROM score WHERE gameid = n AND score in top 10;

I have no idea on how the indexing should be done or if this is the best way, I cant be creating tables for ech new game so I hpe this is the correct way.

Any help will be greatly appreciated.

Thanks
Chris.

Your create table looks good, but I'd recommend that you don't call your time column "time", but instead "gametime" or something like that. time is reserved word and can cause you problems. Also, you may want to call the table gamescores or something like that, just so your table and column aren't named the same. If you want to have the columns indexed properly, you can do something like this when you create the table.

CREATE TABLE gamescores (
id INT AUTO_INCREMENT, PRIMARY KEY(id),
gameid INT NOT NULL REFERENCES game (id),
playerid INT NOT NULL REFERENCES player (id),
score INT NOT NULL,
gametime DATETIME,
INDEX(score),
INDEX(gameid),
INDEX(playerid)
);

I assume you'll be searching on all of those columns. You may even want to index gametime if you'll be using that in queries. Also, I don't use the "REFERENCES" very often with MySQL, so I'm not sure about the syntax on that part of the create. Depending on where you want the data control, you could just take the REFERENCES out.

As for the SELECT:

SELECT playerid,score FROM gamescores WHERE gameid = n ORDER BY score DESC LIMIT 10

That will give you the top 10 scores (assuming that the highest score is the best)

Hope that helps!

Thanks for that DPAK.
Yes highest is best, at least for now, and if that changes then I will have to do some re-eng on the whole project or change the way the games are scored.
I assume the index on score will prevent a complete table scan each time, is that correct?

Chris.

Yes, you should always index any columns that you may use as conditions in your query. So in this case, the gameid definitely should be indexed and I think that having the ORDER BY column indexed also speeds the sorting of the results, but I could be wrong on that.