# GROUP BY usage

**URL:** <https://forums.percona.com/t/group-by-usage/1798>\
**Category:** Other MySQL® Questions\
**Created:** [April 19, 2012, 1:43am UTC](https://forums.percona.com/t/group-by-usage/1798 "2012-04-19T01:43:33Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![manishfr](https://avatars.discourse-cdn.com/v4/letter/m/3d9bf3/32.png) [@manishfr](https://forums.percona.com/u/manishfr)\
**Post date:** [April 19, 2012, 1:43am UTC](https://forums.percona.com/t/group-by-usage/1798/1 "2012-04-19T01:43:33Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![manishfr](https://avatars.discourse-cdn.com/v4/letter/m/3d9bf3/32.png) [@manishfr](https://forums.percona.com/u/manishfr)\
**Post date:** [April 19, 2012, 1:50am UTC](https://forums.percona.com/t/group-by-usage/1798/2 "2012-04-19T01:50:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [April 19, 2012, 5:47am UTC](https://forums.percona.com/t/group-by-usage/1798/3 "2012-04-19T05:47:13Z")

</div>

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: [URL][MySQL Tools and Management Software to Perform System Tasks by Percona](https://tools.percona.com/query/P2SJIvZW/advice%5B/URL%5D)