You didn't include an EXPLAIN of your query (please use \G so that it is easy to read), but my guess is that MySQL decided to use a serial search rather than use an index. It is retrieved 1/4 of your records, and that's after applying the MONTH() test. The BETWEEN clause is the only place it can use an index, and it probably doesn't have a high enough cardinality to be used.
Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com >-----Original Message----- >From: ?? [mailto:dllizh...@gmail.com] >Sent: Tuesday, July 13, 2010 10:30 AM >To: mysql@lists.mysql.com >Subject: query executes very slow in a table with 2m records > >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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
