At 09:29 AM 7/13/2010, æå¾ wrote:
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.
It is because you are using MONTH(quoteDate) which means it has to go
through all the rows. You should add a compound index to QuoteDate,QuoteTime.
Then Try
select count(*) from fxrate where quotedate between '2010-06-01' and
'2010-06-30' and quotetime between '06:00:00' and '19:00:00'
I'm not sure why you have two columns for storing the date and time. I
would have used one column QuoteDateTime as DateTime.
I also don't know why you have all those partitions for a small 2m row table.
Mike
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
