Why not using index?
Posted by:
Date: September 24, 2009 07:00PM
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 .
Subject
Views
Written By
Posted
Why not using index?
2885
September 24, 2009 07:00PM
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.