Performance in a 1 million row MySQL-table in a chess game scenario

Question

Say I run a game website where the users play chess to each other. I have a MySQL-table that contain all the games with their individual chess moves:

Games table (psuedo syntax):

gameId INT
player1Id INT
player2Id INT
gameEnded DATETIME NULL
gameNotation TEXT

A couple of rows can look like:

30021, 2001, 3020, '2013-08-01 12:00:00', '1. e4 e5 2. Nf3 Nf6'
30022, 3020, 2001, NULL, '1. d4'

The gameNotation field can become quite large with 100 moves or more.

To my question: My table who is similar to the above contains 1 million rows and counting, which makes it challenging performance-wise.

How would you partition this table (MySQL 6.5)? Or would you perhaps move the gameNotation column to its own table? I have both ongoing and ended games in the same table, maybe I should move them apart? I'm not sure what would make best sense from a performance perspective.

Thanks for your time!

Answer 1

Yes, probably.

gameId INT
player1Id INT
player2Id INT
gameEnded DATETIME NULL
gameNotation TEXT

I would likely break it out to:

Games:

id INT NOT NULL <-PK,AUTOINCREMENT
white_player_id INT NOT NULL
black_player_id INT NOT NULL
gameEnded DATETIME

Moves:

id INT NOT NULL <-PK,AUTOINCREMENT
game_id INT NOT NULL
move VARCHAR(9) NOT NULL //Change length if necessary