Question 21332 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL JOIN query needs over 15s to run – Mysql

I have a pretty big SQL query to get data from multiple database tables. I use the ON condition to check if the guild_ids are always the same and in some cases, he check’s for an user_id too.

That is my query:

SELECT
    SUM( f.guild_id = 787672220503244800 AND f.winner_id LIKE '%841827102331240468%' ) AS guild_winner,
    SUM( f.winner_id LIKE '%841827102331240468%' ) AS win_sum,
    m.message_count,
    r.bypass_role_id,
    i.real_count,
    i.total_count,
    i.bonus_count,
    i.left_count 
FROM
    guild_finished_giveaways AS f
    JOIN guild_message_count AS m
    JOIN guild_role_settings AS r
    JOIN guild_invite_count AS i ON m.guild_id = f.guild_id 
    AND m.user_id = 841827102331240468 
    AND r.guild_id = f.guild_id 
    AND i.guild_id = f.guild_id 
    AND i.user_id = m.user_id

But it runs pretty slow, with over 15s. I can’t see why it needs so long.

I figured out that if I remove the "guild_invite_count" JOIN, it’s pretty fast again. Do I have some simple error here that I don’t see? Or what could be the issue?

Tags:

2

Answers


  1. 0

    Each JOIN expression needs it’s own ON. Don’t wait until the end for this. As it was, the server was forced to build up a cartesian product of all those tables before narrowing them down again, and I’m surprised the query ran at all (I’d expect a syntax error for missing ON clauses).

    FROM guild_finished_giveaways AS f
  JOIN guild_message_count AS m ON m.guild_id = f.guild_id 
  JOIN guild_role_settings AS r ON r.guild_id = f.guild_id
  JOIN guild_invite_count AS i ON i.guild_id = f.guild_id 
      AND i.user_id = m.user_id
WHERE m.user_id = 841827102331240468

    It’s also more than a little odd to use SUM() or any other aggregate function in the same query as non-aggregated values without a GROUP BY clause.

    Login or Signup to reply.
  2. 0

    Are you using InnoDB?

    Does every table have a PRIMARY KEY?

    These may help:

    m:  PRIMARY KEY(user_id)  -- assuming that is unique in that table
f:  INDEX(guild_id,  winner_id)
r:  INDEX(guild_id,  bypass_role_id)
i:  INDEX(user_id,)

    It looks like some tables should not be separate — perhaps r,i,f could be combined? (I need to see SHOW CREATE TABLE to say more.)

    Do NOT have a commalist in winner_id. Instead have another table with one row per winner per game (or whatever it is a winner of). Perhaps just to columns like a Many-to-many mapping table.

    Noting that the execution is likely to start with m and then go next to i let’s improve on Joel’s suggestion:

    FROM   guild_message_count      AS m
  JOIN guild_invite_count       AS i ON i.user_id  = m.user_id
  JOIN guild_finished_giveaways AS f ON f.guild_id = m.guild_id 
  JOIN guild_role_settings      AS r ON r.guild_id = m.guild_id
WHERE m.user_id = 841827102331240468

    Note that 3 tables are joined on guild_id; but only 2 = are needed.

    SUM without GROUP BY sums up the entire resultset (after JOINing). But you have 6 non-aggregates, so you need to GROUP BY all 6.

    But that may lead to grossly inflated sums. Maybe you need to do the aggregation just over f first since that is where you are summing. Then JOIN to the rest??

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search