我想知道是否有方法优化此查询:
SELECT Avg(selloff1),
YEAR(dt),
weekno
FROM bbproduct
GROUP BY YEAR(dt),
weekno
CREATE TABLE `bbproduct` (
`id` bigint(20) NOT NULL,
`CUSTNO` decimal(6,0) unsigned zerofill NOT NULL default '000000',
`DEPTNO` decimal(1,0) unsigned zerofill NOT NULL default '0',
`PRODNO` decimal(4,0) unsigned zerofill NOT NULL default '0000',
`DT` date NOT NULL,
`ARDATE8N` decimal(8,0) unsigned zerofill NOT NULL default '00000000',
`STORENO` char(6) NOT NULL default '0',
`DAYCODE` decimal(1,0) unsigned zerofill NOT NULL default '0',
`WEEKNO` decimal(2,0) unsigned zerofill NOT NULL default '00',
`WKOFMONTH` decimal(2,0) unsigned zerofill NOT NULL default '00',
`QUANTITY` decimal(7,0) NOT NULL default '0',
`CARRYOVER` decimal(7,0) NOT NULL default '0',
`SELLOFF1` decimal(7,0) NOT NULL default '0',
`ONHANDQTY` decimal(7,0) NOT NULL default '0',
`KNOWNLOSS` decimal(7,0) NOT NULL default '0',
`ORDER` decimal(7,0) NOT NULL default '0',
`FAMLYCD` decimal(4,0) NOT NULL default '0',
`ROUTENO` decimal(4,0) NOT NULL default '0',
`AGENCYNO3N` decimal(3,0) NOT NULL default '0',
`RETURNS` decimal(7,0) NOT NULL,
`LASTUPDATE` datetime NOT NULL,
PRIMARY KEY (`CUSTNO`,`DEPTNO`,`PRODNO`,`DT`),
KEY `in_bbproduct_custnodeptnoprodno` (`CUSTNO`,`DEPTNO`,`PRODNO`),
KEY `in_bbproduct_deptnocustno` USING BTREE (`DEPTNO`,`CUSTNO`),
KEY `in_bbproduct_custno` USING BTREE (`CUSTNO`),
KEY `in_bbproduct_dt` USING BTREE (`DT`),
KEY `in_bbproduct_routeno` (`ROUTENO`),
KEY `in_bbproduct_selloff1` (`SELLOFF1`),
KEY `in_bbproduct_weekno` (`WEEKNO`),
KEY `in_bbproduct_routenoweekno` (`WEEKNO`,`ROUTENO`),
KEY `in_bbproduct_agencyroutedt` USING BTREE (`AGENCYNO3N`,`ROUTENO`,`DT`),
KEY `in_bbproduct_routenoagencyno3n` (`ROUTENO`,`AGENCYNO3N`),
KEY `in_bbproduct_agencyno` (`AGENCYNO3N`),
KEY `in_bbproduct_storeno_prodno` (`STORENO`,`PRODNO`),
KEY `ind_deptno_prodno` (`DEPTNO`,`PRODNO`),
KEY `ind_prodno` (`PRODNO`),
KEY `ind_dt_custno_deptno_prodno` (`DT`,`CUSTNO`,`DEPTNO`,`PRODNO`),
KEY `ind_dt_selloff1` (`DT`,`SELLOFF1`)
) ENGINE=MyISAM AUTO_INCREMENT=9300871 DEFAULT CHARSET=latin1
它需要14秒,表有900万条记录,查询返回15行。我需要将此结果集与其他一些记录一起使用。
我能再做点什么吗?
