Optimising a query for matching multiple values on a related table



  • I am trying to optimise a query for fetching all game IDs where certain units (Archer, Cavalry, etc.) were present.

    I am using the players table, which contains the game_id, and the units table, which contains a player_id foreign key and a unit_type_id foreign key.

    The relevant columns for the tables mentioned above are:

    table: games
    
    id
    1

    table: players
    
    | id | game_id |
    |----|---------|
    | 1  | 1       |
    | 2  | 1       |
    | 3  | 2       |
    | 4  | 2       |
    
    
    table: units
    
    | id  | player_id    | unit_type_id |
    |-----|--------------|--------------|
    | 1   | 1            | 1            |
    | 2   | 1            | 1            |
    | 3   | 1            | 1            |
    | 4   | 1            | 1            |
    | 5   | 1            | 2            |
    | 6   | 1            | 2            |
    | 7   | 1            | 2            |
    | 8   | 1            | 2            |
    | 9   | 2            | 3            |
    | 10  | 2            | 3            |
    | 11  | 2            | 3            |
    | 12  | 2            | 3            |
    
    
    table: unit_types
    
    | id | type              |
    |----|-------------------|
    | 1  | ARCHER            |
    | 2  | BERSERKER         |
    | 3  | CAVALRY           |
    | 4  | CROSSBOWMAN       |
    

    The query I'm currently using, which works, is:

    SELECT
    players.game_id AS gameId,
    GROUP_CONCAT(DISTINCT units.unit_type_id) AS unitTypes
    FROM players
    INNER JOIN units
    ON players.id = units.player_id
    GROUP BY players.game_id
    HAVING
    FIND_IN_SET('1', unitTypes)
    AND
    FIND_IN_SET('2', unitTypes)
    

    I programatically generate the FIND_IN_SET statements.

    Importantly, I want to return only game IDs that contain all specified unit types. This is why WHERE units.unit_type_id IN (1, 2, 3) won't work.

    I have also tried:

    • HAVING SUM(CASE WHEN units.unit_type_id IN (1, 2) THEN 1 END) = 2; - didn't work
    • HAVING SUM(units.unit_type_id IN (10, 11)) = 2; - didn't work
    • Using multiple inner joins, one on each unit type - this was very slow, taking 3 times as long

    MySQL version: 5.7.37

    https://www.db-fiddle.com/f/tXgTCr88pbQVRA4evimrnb/1

    Is there a better way of doing this to improve performance in terms of speed and resources? Any suggestions would be appreciated.

    Thanks!



  • I hope I understood correctly the question, if not please let me know.

    Try:

    SELECT p.game_id AS gameId
    FROM players p
    INNER JOIN units u ON p.id = u.player_id
    WHERE u.unit_type_id in (1,2)
    GROUP BY p.game_id
    HAVING COUNT(DISTINCT u.unit_type_id) =2 ;
    

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f90fd3d4c5300a4e4ed3e7323243f89d

    This will return the players.game_id which both have unit_type_id (1,2). If you have three or more u.unit_type_id in you have to change HAVING COUNT(DISTINCT u.unit_type_id) =2 ; to the number of the records in the in condition




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2