大家好,在mysql查询时,遇到此查询使用了临时表,请问有什么办法可以解决的。
表结构为:
CREATE TABLE `readmerter400601` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`meterTxID` char(4) NOT NULL,
`current` float(10,3) NOT NULL,
`voltage` float(10,2) NOT NULL,
`power` float(10,1) NOT NULL,
`kwh` float(10,3) NOT NULL,
`temperature` int(10) NOT NULL,
`timestamp` datetime NOT NULL,
`reverseid` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `indexreverse` (`reverseid`) USING BTREE,
KEY `indextimestamp` (`timestamp`,`reverseid`,`kwh`) USING BTREE
)
查询语句为:
SELECT `timestamp`,kwh,max(kwh) FROM `readmerter400601` WHERE `timestamp` BETWEEN '2013-06-01' and '2013-06-30' GROUP BY day(`timestamp`) ORDER BY null;
通过explain得到的信息:
+----+-------------+------------------+-------+----------------+----------------+---------+------+-------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+-------+----------------+----------------+---------+------+-------+-------------------------------------------+
| 1 | SIMPLE | readmerter400601 | range | indextimestamp | indextimestamp | 8 | NULL | 43147 | Using where; Using index; Using temporary |
+----+-------------+------------------+-------+----------------+----------------+---------+------+-------+-------------------------------------------+
非常感谢!MySQL
表结构为:
CREATE TABLE `readmerter400601` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`meterTxID` char(4) NOT NULL,
`current` float(10,3) NOT NULL,
`voltage` float(10,2) NOT NULL,
`power` float(10,1) NOT NULL,
`kwh` float(10,3) NOT NULL,
`temperature` int(10) NOT NULL,
`timestamp` datetime NOT NULL,
`reverseid` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `indexreverse` (`reverseid`) USING BTREE,
KEY `indextimestamp` (`timestamp`,`reverseid`,`kwh`) USING BTREE
)
查询语句为:
SELECT `timestamp`,kwh,max(kwh) FROM `readmerter400601` WHERE `timestamp` BETWEEN '2013-06-01' and '2013-06-30' GROUP BY day(`timestamp`) ORDER BY null;
通过explain得到的信息:
+----+-------------+------------------+-------+----------------+----------------+---------+------+-------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+-------+----------------+----------------+---------+------+-------+-------------------------------------------+
| 1 | SIMPLE | readmerter400601 | range | indextimestamp | indextimestamp | 8 | NULL | 43147 | Using where; Using index; Using temporary |
+----+-------------+------------------+-------+----------------+----------------+---------+------+-------+-------------------------------------------+
非常感谢!MySQL
`id` INT(11) NOT NULL AUTO_INCREMENT,
`meterTxID` CHAR(4) NOT NULL,
`current` FLOAT(10,3) NOT NULL,
`voltage` FLOAT(10,2) NOT NULL,
`power` FLOAT(10,1) NOT NULL,
`kwh` FLOAT(10,3) NOT NULL,
`temperature` INT(10) NOT NULL,
`timestamp` DATETIME NOT NULL,
`reverseid` INT(11) NOT NULL,
`aid` INT(2),
PRIMARY KEY (`id`),
KEY `indexreverse` (`reverseid`) USING BTREE,
KEY `indextimestamp` (aid,`reverseid`,`kwh`,`timestamp`) USING BTREE
)
增加AID,保存day(`timestamp`) EXPLAIN
SELECT `timestamp`,kwh,MAX(kwh) FROM `readmerter400601` WHERE `timestamp` BETWEEN '2013-06-01' AND '2013-06-30' GROUP BY aid ORDER BY NULL;
那请问aid中怎样保存day(timestamp)的值好点,要考虑到年,月,日吧
根据需要修改AID中的值