GROUP BY usage

[manishfr]

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)

[manishfr]

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

[xaprb]

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

[manishfr]

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

[xaprb]

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!