MySQL performance with GROUP BY and JOIN -

after spending lot of time variants question i'm wondering if can me optimize query or indexes.

i have 3 temp tables ref1, ref2, ref3 defined below, ref1 , ref2 each having 6000 rows , ref3 3 rows:

create temporary table ref1 (   id int not null auto_increment,   val int,   primary key (id) ) engine = memory; 

the slow query against table so, 1m rows:

create table t1 (   d datetime not null,   id1 int not null,   id2 int not null,   id3 int not null,   x int null,   primary key (id1, d, id2, id3) ) engine = innodb; 

the query in question:

select id1, sum(x)   t1   inner join ref1 on ref1.id = t1.id1   inner join ref2 on ref2.id = t1.id2   inner join ref3 on ref3.id = t1.id3   d between '2011-03-01' , '2011-04-01'   group id1; 

the temp tables used filter result set down items user looking for.

explain

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+ | id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows |                           | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+ |  1 | simple      | ref1  |    | primary       | null    | null    | null             | 6000 | using temporary; using filesort | |  1 | simple      | t1    | ref    | primary       | primary | 4       | med31new.ref1.id |   38 | using                     | |  1 | simple      | ref3  |    | primary       | null    | null    | null             |    3 | using where; using join buffer  | |  1 | simple      | ref2  | eq_ref | primary       | primary | 4       | med31new.t1.id2  |    1 |                                 | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+ 

(on different system ~5m rows explain show t1 first in list, "using where; using index; using temporary; using filesort")

is there obvious i'm missing prevent temporary table being used?

add index on date. since criteria of first table, , others joins, optimized against date first... joins secondary.