query executes very slow in a table with 2m records

--001636164aeb0f2ddd048b45b3b2
Content-Type: text/plain; charset=ISO-8859-1

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.

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

--001636164aeb0f2ddd048b45b3b2--

Re: query executes very slow in a table with 2m records

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) =3D 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=20
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=20
'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=20
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=3DMYISAM DEFAULT CHARSET=3Dutf8
>/*!50100 PARTITION BY LIST (MONTH(quoteDate))
>SUBPARTITION BY HASH (day(quoteDate))
>SUBPARTITIONS 16
>(PARTITION `one` VALUES IN (1) ENGINE =3D MyISAM,
> PARTITION two VALUES IN (2) ENGINE =3D MyISAM,
> PARTITION three VALUES IN (3) ENGINE =3D MyISAM,
> PARTITION four VALUES IN (4) ENGINE =3D MyISAM,
> PARTITION five VALUES IN (5) ENGINE =3D MyISAM,
> PARTITION six VALUES IN (6) ENGINE =3D MyISAM,
> PARTITION seven VALUES IN (7) ENGINE =3D MyISAM,
> PARTITION eight VALUES IN (8) ENGINE =3D MyISAM,
> PARTITION nine VALUES IN (9) ENGINE =3D MyISAM,
> PARTITION ten VALUES IN (10) ENGINE =3D MyISAM,
> PARTITION eleven VALUES IN (11) ENGINE =3D MyISAM,
> PARTITION twelve VALUES IN (12) ENGINE =3D MyISAM) */
>
>
>--
>Regards,
>Zheng Li


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: query executes very slow in a table with 2m records

You didn't include an EXPLAIN of your query (please use \G so that it is easy
to read), but my guess is that MySQL decided to use a serial search rather
than use an index. It is retrieved 1/4 of your records, and that's after
applying the MONTH() test. The BETWEEN clause is the only place it can use an
index, and it probably doesn't have a high enough cardinality to be used.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com


>-----Original Message-----
>From: ?? [mailto:dllizheng@gmail.com]
>Sent: Tuesday, July 13, 2010 10:30 AM
>To: mysql@lists.mysql.com
>Subject: query executes very slow in a table with 2m records
>
>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.
>
>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=gcdmg-mysql-2@m.gmane.org

Re: query executes very slow in a table with 2m records

On Tue, Jul 13, 2010 at 8:22 AM, mos 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) =3D 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 wo=
uld
> 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.

--=20
Rob Wultsch
wultsch@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: query executes very slow in a table with 2m records

At 10:36 AM 7/13/2010, Rob Wultsch wrote:
>On Tue, Jul 13, 2010 at 8:22 AM, mos wrote:
> > At 09:29 AM 7/13/2010, æ ŽÃ¥Â=BE =C2 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) =3D 6
> >> =C2 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=
=20
> 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=
=20
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=20
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=3Dgcdmg-mysql-2@m.gmane.o rg