Hello, 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) */ -- Regards, Zheng Li
