help~the statement don't use index

[kelvin chou]

Hi I am confuse about the statement doesn't use the index.Pls help~

this is my statement.. I don't know where is wrong, Can someone please help me ?

mysql> explain SELECT MAX( RoundSerial ) AS MAX, MIN( RoundSerial ) AS MIN FROM tests WHERE StartDate >= '2011-10-31' AND StartDate <= '2011-11-04' AND Result > '0';
+----+-------------+-------+------+------------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | tests | ALL | RoundDate,Result | NULL | NULL | NULL | 980189 | Using where |
+----+-------------+-------+------+------------------+------+---------+------+--------+-------------+

show to repeat
CREATE TABLE `tests` (
`UserID` int(10) unsigned NOT NULL auto_increment,
`RoundSerial` int(10) unsigned NOT NULL DEFAULT '0',
`StartDate` date NOT NULL DEFAULT '1970-01-01',
`Result` smallint(5) NOT NULL DEFAULT '0',
PRIMARY KEY (`UserID`),
KEY `UserID` (`UserID`,`StartDate`),
KEY `RoundSerial` (`RoundSerial`),
KEY `RoundDate` (`StartDate`),
KEY `Result` (`Result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into tests(RoundSerial,StartDate,Result) values((select rand()*10000),date_add(curdate(),interval-(rand()*45)day),round(rand(),0));
insert into tests(RoundSerial,StartDate,Result) select rand()*10000,date_add(curdate(),interval-(rand()*45)day),round(rand(),0) from tests as a,tests as b,test as c,test as d;



Edited 1 time(s). Last edit at 11/08/2011 02:04AM by kelvin chou.