From: mos (mos99fastmail.fm)
Date: Tue Jul 13 2010 - 13:53:55 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

At 10:36 AM 7/13/2010, Rob Wultsch wrote:
>On Tue, Jul 13, 2010 at 8:22 AM, mos <mos99fastmail.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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]