Why not using index?

I have an InnoDB table

CREATE TABLE `historico` (
`Symbol_ID` char(5) NOT NULL,
`Exchange_ID` char(4) NOT NULL,
`Date` datetime NOT NULL,
`Open` double NOT NULL,
`Close` double NOT NULL,
`Low` double NOT NULL,
`High` double NOT NULL,
`Volume` double NOT NULL,
/* Keys */
PRIMARY KEY (`Symbol_ID`, `Exchange_ID`, `Date`),
/* Foreign keys */
CONSTRAINT `FgnCompaniesHist`
FOREIGN KEY (`Symbol_ID`, `Exchange_ID`)
REFERENCES `companies`(`Symbol`, `Exchange`)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE = InnoDB;

CREATE INDEX `IdxHistDate`
ON `historico`
(`Date`);

------------------

Now:

explain EXTENDED SELECT `Symbol_ID`, `Close`
FROM `Historico`
WHERE `Date`>='2008-04-19 15:59:00' AND `Date`<='2008-04-20 15:59:00'
ORDER BY `Date`;

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE Historico range IdxHistDate IdxHistDate 8 1 100 Using where



But if I change the date range:

explain EXTENDED SELECT `Symbol_ID`, `Close`
FROM `Historico`
WHERE `Date`>='2008-04-19 15:59:00' AND `Date`<='2008-08-20 15:59:00'
ORDER BY `Date`;

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE Historico ALL IdxHistDate 139977352 24.92 Using where; Using filesort

Where is the problem in handling larger result-sets? (using between gives the same problem). Table was checked status and is ok. It has almost 140 millon records.

Regards, Mauro.

EDIT: Using 5.1.39



Edited 1 time(s). Last edit at 09/24/2009 07:16PM by .