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?
Kind Regards, Bjorn