Re: JOIN with GROUP BY, without PRIMARY KEY

[Jerome Asselin]

Thanks for the reply. How about if I allow to restrict B.gr to a specific value?...

explain select A.id, B.gr, sum(B.score)
from A join B where B.gr = 1 and B.val = A.val
group by A.id;

+----+-------------+-------+------+-----------------------+---------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------+---------+---------+-------------------+------+----------------------------------------------+
| 1 | SIMPLE | B | ref | PRIMARY,gr_val_sc,val | PRIMARY | 4 | const | 1 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | A | ref | val | val | 4 | Transition3.B.val | 2 | Using where |
+----+-------------+-------+------+-----------------------+---------+---------+-------------------+------+----------------------------------------------+

Hmm... Still using temporary and filesort... But if I force the "id" index and use limit, then I get:

explain select A.id, B.gr, sum(B.score)
from A force index (id) join B on A.val = B.val where B.gr = 1
group by A.id limit 2;

+----+-------------+-------+--------+-----------------------+---------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------+---------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | A | index | NULL | id | 5 | NULL | 4 | |
| 1 | SIMPLE | B | eq_ref | PRIMARY,gr_val_sc,val | PRIMARY | 7 | const,Transition3.A.val | 1 | Using where |
+----+-------------+-------+--------+-----------------------+---------+---------+-------------------------+------+-------------+

Bingo! The row count for table A is higher, though. I wonder how that'll perform in my real life data. I'll keep you posted. Meanwhile any additional comments are apreciated.