heres my sql query: EXPLAIN SELECT t1.szs_guige,SUM(t1.szs_shuliang) FROM szs_ck_ruku_items t1,szs_ck_ruku t2 WHERE t1.szs_rukubianhao = t2.szs_rukubianhao AND t2.szs_date>'2010-10-23' GROUP BY t1.szs_guige ORDER BY null
result: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range PRIMARY,date date 3 \N 177 Using where; Using index; Using temporary 1 SIMPLE t1 ref PRIMARY PRIMARY 32 szs_jihuadan.t2.szs_rukubianhao 4 table structure below: CREATE TABLE `szs_ck_ruku` ( `szs_rukubianhao` varchar(10) NOT NULL, `szs_kuhao` varchar(10) NOT NULL, `szs_kuwei` varchar(10) NOT NULL, `szs_xianghao` varchar(4) NOT NULL, `szs_caozuoyuan` varchar(10) NOT NULL, `szs_date` date NOT NULL, PRIMARY KEY (`szs_rukubianhao`,`szs_kuhao`,`szs_kuwei`), KEY `date` (`szs_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `szs_ck_ruku_items` ( `szs_rukubianhao` varchar(10) NOT NULL, `szs_kuhao` varchar(10) NOT NULL, `szs_kuwei` varchar(10) NOT NULL, `szs_shengchanhao` varchar(50) NOT NULL, `szs_guige` varchar(100) NOT NULL, `szs_tuhao` varchar(100) NOT NULL, `szs_shuliang` int(8) NOT NULL, `szs_beizhu` text NOT NULL, PRIMARY KEY (`szs_rukubianhao`,`szs_kuhao`,`szs_kuwei`,`szs_shengchanhao`,`szs_guige`,`szs_tuhao`), KEY `GG` (`szs_guige`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 how to avoid using temporary? i am confuzed...... thanks