0

I have a developer who has created a query joining 3 tables in a method that I have never seen before. This query is working although extremely slow. I am trying to understand how this query is functioning because it seems to be extremely poorly designed. I have tried to create a working sample that will hopefully clearly show the issue.

SELECT A.Col1, C.Col3
    FROM TABLEA A 
        INNER JOIN TABLEB B
            ON A.... = B....
        LEFT JOIN TABLEC C
            ON A.... = C....  AND A.... = C....
                AND B... = C.... AND B... = C.... AND B... = C... AND B... = C....

Notice how on the LEFT JOIN they have pulled in conditions from both TABLE A and TABLE B to join to C. Is this creating some sort of derived or temporary table in the background?

  • Please include the output of EXPLAIN EXTENDED (see also here for the query in question. Also, please give the output of SHOW CREATE TABLE A\G (and for tables B and C). Also, please use the formatting widgets at the top of the text box where you ask or edit (you can always edit) your own questions. – Vérace Jul 30 '19 at 14:58
  • Do you have indexes (B....), (C...., C....)? – Rick James Jul 31 '19 at 23:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.