MySQL Forums
Forum List  »  Quality Assurance

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 .

Options: ReplyQuote


Subject
Views
Written By
Posted
Why not using index?
2720
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.