Mysql – A SQL query optimzation issue with ORDER BY

An SQL query optimzation issue with ORDER BY

Current status
I have a database of Chess games and their corresponding moves (stored as strings called fens). I have two main tables 'Game' and 'GamePosition'. GamePosition has an index on the fen column and Game has an index on white_elo. I currently have 170471 games and 14813401 positions. I'm running mysql 5.7.28.

Object
I'm trying to fetch top rated games based on elo rating of the players. I've simplified my query a bit here, but the point and performance problem is the same.

SELECT Game.id
FROM Game
JOIN GamePosition ON Game.id = game_id
WHERE fen = 'rnbqkbnr/pppppppp/8/8/3P4/8/PPP1PPPP/RNBQKBNR'
ORDER BY white_elo DESC
LIMIT 10

This query tends to be a bit slow (1.2 s) if I get a lot of results (typically the first move gives 67k+ results). Since I plan to exand the database 10x at least, I want to optimize my query. I've run a EXPLAIN which shows that it needs to do a filesort before finding the top rated games on all the results. This seems to be the issue. Removing the ORDER BY from the query makes it superfast (0.0008 s).

Any ideas if it's possible to optimize the query, or if I could store the data differently?

UPDATE:

       Table: GamePosition
Create Table: CREATE TABLE `GamePosition` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL,
  `fen` varchar(100) NOT NULL,
  `move_color` char(1) NOT NULL,
  `castling_rights` varchar(4) NOT NULL,
  `en_passant_square` varchar(2) NOT NULL,
  `half_move` smallint(6) NOT NULL,
  `full_move` smallint(6) NOT NULL,
  `timestamp` time DEFAULT NULL COMMENT 'time in video',
  `move` varchar(10) DEFAULT NULL,
  `from_square` varchar(4) DEFAULT NULL,
  `to_square` varchar(4) DEFAULT NULL,
  `parent_position` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `position_ibfk_1` (`game_id`),
  KEY `fen` (`fen`),
  KEY `parent_position` (`parent_position`),
  CONSTRAINT `game_key` FOREIGN KEY (`game_id`) REFERENCES `Game` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

       Table: Game
Create Table: CREATE TABLE `Game` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pgn` text NOT NULL,
  `white_id` int(11) NOT NULL,
  `black_id` int(11) NOT NULL,
  `white_elo` int(11) DEFAULT NULL,
  `black_elo` int(11) DEFAULT NULL,
  `result` tinyint(4) DEFAULT NULL COMMENT '0=white, 1=draw 2=black',
  `date` date DEFAULT NULL,
  `event_id` int(11) NOT NULL,
  `eco` varchar(5) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `event_key` (`event_id`),
  KEY `black_key` (`black_id`),
  KEY `white_key` (`white_id`),
  CONSTRAINT `black_key` FOREIGN KEY (`black_id`) REFERENCES `Player` (`id`),
  CONSTRAINT `event_key` FOREIGN KEY (`event_id`) REFERENCES `Event` (`id`),
  CONSTRAINT `white_key` FOREIGN KEY (`white_id`) REFERENCES `Player` (`id`)
)

       Table: Player
Create Table: CREATE TABLE `Player` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique` (`id`)
)

Actual Query

SELECT GamePosition.id AS position_id, Game.id AS id,
       white.name AS white, black.name AS black, Game.id,
       white_elo, black_elo, result, date
    FROM Game
    JOIN GamePosition ON Game.id = game_id
    JOIN Player white ON white.id = white_id
    JOIN Player black ON black.id = black_id
    WHERE fen = '$fen'
    ORDER BY white_elo+black_elo DESC
    LIMIT $limit
    OFFSET $offset";

Kind Regards,
Bjorn