JOIN with GROUP BY, without PRIMARY KEY

[Jerome Asselin]

Below is a simplified example of what I want to do. I want to get rid of "Using temporary; Using filesort" in the EXPLAIN query below. Table A has no primary key. How should I index the tables?

#drop table if exists A; drop table if exists B;
create table A (id int, val char(3), key (id), key (val), key id_val (id,val));
insert into A values (5, 'one'),(5, 'one'),(10, 'one'),(10, 'two');
create table B (gr int, val char(3), score int, primary key (gr, val), key (val), unique gr_val_sc (gr, val, score));
insert into B values (1, 'one', 1),(1, 'two', 2),(2, 'one', 3),(2, 'two', 4);

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

+----+-------------+-------+-------+---------------+-----------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | A | index | val | id_val | 9 | NULL | 4 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | B | index | val | gr_val_sc | 12 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+----------------------------------------------+
2 rows in set (0.00 sec)

EDIT: I'm using MySQL 4.1.12 on RHEL 4 AS.



Edited 1 time(s). Last edit at 05/12/2006 11:42AM by Jerome Asselin.