MySQL Forums
Forum List  »  Optimizer & Parser

query executes slowly in a table with 2m records
Posted by: Zheng Li
Date: July 13, 2010 08:38AM

There are more than 2m records in the table -- fxrate.
I create patitions, indexes, but it still takes me about 7 minutes to execute the following query
SELECT COUNT(*)
FROM fxrate
WHERE MONTH(quoteDate) = 6
AND quoteHourMinSec BETWEEN "06:00:00" AND "19:00:00"

result: 647337

How can I improve the performace?
Thanks in advance.

OS: windows xp pro sp3
MySQL: 5.1.48
CPU: Core2 Duo 2.1G
Memory: 2G



CREATE TABLE `fxrate` (
`priceId` VARCHAR(128) DEFAULT NULL,
`buySwap` DOUBLE DEFAULT NULL,
`askRate` DOUBLE NOT NULL,
`bidRate` DOUBLE NOT NULL,
`changeRate` DOUBLE DEFAULT NULL,
`currcncyPairHalf` VARCHAR(128) DEFAULT NULL,
`currcncyPairJp` VARCHAR(128) DEFAULT NULL,
`currencyPair` VARCHAR(16) NOT NULL,
`highRate` DOUBLE DEFAULT NULL,
`lowRate` DOUBLE DEFAULT NULL,
`openRate` DOUBLE DEFAULT NULL,
`quoteTime` DATETIME NOT NULL,
`sellSwap` DOUBLE DEFAULT NULL,
`tradable` TINYINT(1) DEFAULT NULL,
`quoteDate` DATE DEFAULT NULL,
`quoteHourMinSec` TIME DEFAULT NULL,
`fileName` VARCHAR(256) NOT NULL,
`packetNo` INT(11) NOT NULL,
`insertTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `askRate_idx` (`askRate`),
KEY `fileName_idx` (`fileName`),
KEY `quoteHourMinSec_idx` (`quoteHourMinSec`),
KEY `priceId_idx` (`priceId`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (MONTH(quoteDate))
SUBPARTITION BY HASH (day(quoteDate))
SUBPARTITIONS 16
(PARTITION `one` VALUES IN (1) ENGINE = MyISAM,
PARTITION two VALUES IN (2) ENGINE = MyISAM,
PARTITION three VALUES IN (3) ENGINE = MyISAM,
PARTITION four VALUES IN (4) ENGINE = MyISAM,
PARTITION five VALUES IN (5) ENGINE = MyISAM,
PARTITION six VALUES IN (6) ENGINE = MyISAM,
PARTITION seven VALUES IN (7) ENGINE = MyISAM,
PARTITION eight VALUES IN (8) ENGINE = MyISAM,
PARTITION nine VALUES IN (9) ENGINE = MyISAM,
PARTITION ten VALUES IN (10) ENGINE = MyISAM,
PARTITION eleven VALUES IN (11) ENGINE = MyISAM,
PARTITION twelve VALUES IN (12) ENGINE = MyISAM) */

Options: ReplyQuote


Subject
Views
Written By
Posted
query executes slowly in a table with 2m records
3013
July 13, 2010 08:38AM


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.