Search code examples
mysqlsqljoininner-join

MySQL very long query, inner join

i developed a website where two team can face each other. The first team create a match search m for a certain value (stack), with status = search. The first team has a elo (level) attribute. When the second team (which have a elo of 150) will search a match, we execute the following query:

SELECT m.id, m.value, m.teamOneScore, m.teamTwoScore, m.date, m.status, m.detail, m.team_one_id, m.team_two_id 
FROM user_match m
INNER JOIN team t ON m.team_one_id = t.id 
WHERE t.id = m.team_one_id 
    AND t.id <> 3        // You don't want to play against your own team
    AND t.elo <= 200     // 50 of elo range for example
    AND t.elo >= 100 
    AND m.value = '0.5'  
    AND m.status = 'search'
LIMIT 1

The problem is that this query is very long even if we only have 15000 user_match (there are only 40 rows that answer the requirements with value = 0.5, status = search) and 15000 teams (there are 5570 teams that answer the requirements with elo and id) I tried to EXPLAIN the request, and here is the result:

enter image description here

If there is no opponent when a user searches a match, the request can take like 3 minutes, and this time will be reduce when the same request is executed many times (cache ?), and it's faster if there is an opponent.

The last thing I could precise is that user_match table contains a detail attribute that contains an heavy json_array ONLY IF the match status = ended

Do you know if it's the regular execution time ? What could I do to improve that ?

Thanks !

Solution

  • First, don't repeat the condition on t.id = m.team_one_id. So, this is your query:

    SELECT m.id, m.value, m.teamOneScore, m.teamTwoScore, m.date, m.status, m.detail, m.team_one_id, m.team_two_id 
FROM user_match m INNER JOIN
     team t
     ON m.team_one_id = t.id 
WHERE t.id <> 3 AND     // You don't want to play against your own team
      t.elo <= 200 AND    // 50 of elo range for example
      t.elo >= 100 AND
      m.value = '0.5' AND
      m.status = 'search'
LIMIT 1;

    Second, the query looks suspicious because you have LIMIT with no ORDER BY. Typically, you want an ORDER BY so the row you return is guaranteed to be the same on each execution.

    Indexes are how you speed queries. I would start with the following indexes:

    • user_match(status, value, team_one_id)
    • team(id, elo)

    The index on user_match is the best for that table based on the where clause. Note: If value is a number, then do not use single quotes around the 0.5, that can confuse the optimizer.

    The second index on team is not necessary if id is declared as the primary key.