At 10:36 AM 7/13/2010, Rob Wultsch wrote:
On Tue, Jul 13, 2010 at 8:22 AM, mos <mo...@fastmail.fm> 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
>
>
Rob,
A compound index index will not be used after the first range
condition.
That is correct. But MySQL should be able to reference QuoteTime from the
compound index so it doesn't have to access the data file for the search. :-)
I agree about using a single datatype (datetime or
timestamp) and partitioning not being a good idea for only 2M rows.
Yeah, he is making things far too difficult for himself when the solution
is to use a better index.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
