On Tue, Jul 13, 2010 at 8:22 AM, mos <mos99@stripped> wrote:
> At 09:29 AM 7/13/2010, æ Žå¾ wrote:
>> 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
> 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
A compound index index will not be used after the first range
condition. I agree about using a single datatype (datetime or
timestamp) and partitioning not being a good idea for only 2M rows.