MySQL Forums
Forum List  »  Optimizer & Parser

help~the statement don't use index
Posted by: kelvin chou
Date: November 08, 2011 01:54AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
help~the statement don't use index
1831
November 08, 2011 01:54AM
807
November 08, 2011 09:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.