GROUP BY foreign key or primary key?

group-by, mysql, php, primary-key, sql

Solution

You should use the 2 version grouping by the primary key. In general it's better to have order and group by statements based on column(s) of the main table and not the joined ones. If you do order or group by on joined table columns MySQL has to do create temporary tables which based on your datasize could easily go to disk and end up significant performance bottleneck.

Run these explains for the two query to see what I was talking about:

EXPLAIN SELECT name, SUM(mark) AS total FROM users
   LEFT JOIN marks ON marks.user_id = users.id
   GROUP BY marks.user_id;

EXPLAIN SELECT name, SUM(mark) AS total FROM users
   LEFT JOIN marks ON marks.user_id = users.id
   GROUP BY users.id;

Same can be found here: http://sqlfiddle.com/#!2/a8ece/6

Problem

I have two tables : ``` user ======= id name class marks ======= id user_id sub_id mark ``` user table contains the details of user (student) marks table contains the marks of a student in different subjects with subject id I want to fetch name, class and total marks from these tables. I have two queries : ``` 1. SELECT name, class, SUM(mark) AS total FROM user LEFT JOIN marks ON marks.user_id = user.id GROUP BY marks.user_id ///Here in GROUP BY I have used foreign key (marks.user_id) 2. SELECT name, class, SUM(mark) AS total FROM user LEFT JOIN marks ON marks.user_id = user.id GROUP BY user.id ///Here in GROUP BY I have used primary key (user.id) ``` Both gives me required data. My question is which one should I use? Is there any rule which says you should use primary key in group by OR foreign key in group by OR something like that ?

Original source