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