List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:July 13 2010 3:36pm
Subject:Re: query executes very slow in a table with 2m records
View as plain text  
On Tue, Jul 13, 2010 at 8:22 AM, mos <mos99@stripped> wrote:
> 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
>
>


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.

-- 
Rob Wultsch
wultsch@stripped
Thread
query executes very slow in a table with 2m records李征13 Jul
  • Re: query executes very slow in a table with 2m recordsmos13 Jul
    • Re: query executes very slow in a table with 2m recordsRob Wultsch13 Jul
      • Re: query executes very slow in a table with 2m recordsmos13 Jul
  • RE: query executes very slow in a table with 2m recordsJerry Schwartz13 Jul