Announcement

Announcement Module
Collapse
No announcement yet.

GROUP BY usage

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • GROUP BY usage

    I have most of the queries like

    select distinct A.id from tab_a A
    left join tab_b B on A.col_1=B.col_2
    left join tab_c C on B.col_3=C.col_4
    where B.col_2='xyz' and C.col_3='111'
    group by A.name, A.id
    LIMIT 0,10

    # these are dynamic queries generated in java; WHERE clause is decided based on user's inputs
    # mentioned only 3 tables but there are 10.

    Now my questions are

    1. Does GROUP BY has performance issues ?

    2. Why doesn't MySQL check queries if GROUP BY is used but not any aggregate function ? Oracle, SQL server will throw exceptions in such queries.

    3. Isn't EXISTS will be better than JOIN in this scenario as we need to filter out id (which is pk) from parent table (tab_a) based on records matching certain params in child table(s)

  • #2
    In below cases does MySQL use the same execution plan or will create a different plan each time ?

    # BOTH VALUES PROVIDED BY USER
    select distinct A.id from tab_a A
    left join tab_b B on A.col_1=B.col_2
    left join tab_c C on B.col_3=C.col_4
    where B.col_2='xyz' and C.col_3='111'
    group by A.name, A.id
    LIMIT 0,10

    # NONE PROVIDED BY USER
    select distinct A.id from tab_a A
    left join tab_b B on A.col_1=B.col_2
    left join tab_c C on B.col_3=C.col_4
    group by A.name, A.id
    LIMIT 0,10

    # single input for column in TAB_B PROVIDED BY USER
    select distinct A.id from tab_a A
    left join tab_b B on A.col_1=B.col_2
    left join tab_c C on B.col_3=C.col_4
    where B.col_2='xyz'
    group by A.name, A.id
    LIMIT 0,10

    # single input for column in TAB_C PROVIDED BY USER
    select distinct A.id from tab_a A
    left join tab_b B on A.col_1=B.col_2
    left join tab_c C on B.col_3=C.col_4
    where C.col_3='111'
    group by A.name, A.id
    LIMIT 0,10

    Comment


    • #3
      To respond to your first questions,

      1. No, but you do need to understand how to use indexes to try to avoid sorting and temporary tables.

      2. If SQL_MODE includes ONLY_FULL_GROUP_BY, it will throw an error.

      3. Your LEFT JOINs are going to be converted into INNER JOINs because of the = in the WHERE clause. If you think about that, it's impossible for there to be a row where A exists but no B or C exists (which is what a LEFT JOIN enables) because the = will exclude NULLs in tables B or C.

      In addition, the query has several other problems. I ran it through our query analyzer, and you can find the result here: https://tools.percona.com/query/P2SJIvZW/advice

      Comment


      • #4
        Regarding pt 3 This is one of the query ...

        SELECT
        DISTINCT(C.CONTACT_ID)
        FROM
        TAB_A C
        LEFT JOIN TAB_B S ON S.TAB_B_ID=C.TAB_B_ID
        LEFT JOIN TAB_C LS ON LS.TAB_C_ID = C.TAB_C_ID
        LEFT JOIN TAB_D CI ON C.COMPANY_ID = CI.COMPANY_ID
        LEFT JOIN TAB_E CT ON CT.CONTACT_TYPE_ID=C.CONTACT_TYPE
        LEFT JOIN TAB_F F ON F.TAB_F_NO=C.TAB_F_NO
        LEFT JOIN ADDRESS A ON A.FOREIGN_ID=C.CONTACT_ID
        LEFT JOIN TAB_G CS ON C.CONTACT_ID=CS.CONTACT_ID
        LEFT JOIN USERS U ON C.CONTACT_OWNER_ID = U.USER_NO
        WHERE 1=1
        AND (C.CONTACT_TYPE ='3' AND U.USER_NO=1234 AND F.TAB_F_NAME like '%test%' )
        ORDER BY C.CONTACT_ADD_DATE DESC ,CONTACT_ID
        LIMIT 0 , 20

        Comment


        • #5
          Is that an auto-generated query? There are a lot of problems with that query. If it's auto-generated, maybe you can tweak some knobs on the generating software to make it more sane. If it's hand-written, maybe a Support contract with us can help!

          Comment

          Working...
          X