A SQL query optimzation issue with ORDER BY

1 vote
2 answers
63 views
mysql performance optimization order-by tuning
                          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
                        
Asked by Oddbjørn Sjøgren (11 rep)
May 13, 2020, 02:43 PM
Last activity: May 24, 2020, 12:54 AM
A SQL query optimzation issue with ORDER BY

Related Questions
