Partitioning

------=_NextPart_000_0092_01CAB3E3.E96CF320
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable

Iâ€=99d like to know your opinions about partitioning the following =
table. Hereâ€=99s the relevant snippet:

=20

Create Table: CREATE TABLE `prod_price` (

`prod_price_id` varchar(15) NOT NULL DEFAULT '',

`prod_id` varchar(15) DEFAULT NULL,

â€=A6

PRIMARY KEY (`prod_price_id`),

KEY `prod_id` (`prod_id`)

) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8

=20

Hereâ€=99s the deal. The primary key, `prod_price_id`, is rarely =
used. Prices, as you might expect, are fetched by `prod_id`. Both keys =
are randomly generated strings. (Before you ask, I am not a mental =
health professional and am therefore not qualified to judge my =
predecessor.)

=20

How could I partition this table in a useful way?

=20

Regards,

=20

Jerry Schwartz

The Infoshop by Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=20

860.674.8796 / FAX: 860.674.8341

=20

www.the-infoshop.com

=20


------=_NextPart_000_0092_01CAB3E3.E96CF320--

Re: Partitioning

--0050450163335efddb048042564d
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

that's very much gonna depend on what your selects look like. For example, =
a
low-cardinality but often-where'd field makes an interesting candidate, as
such a partitioning will take the size of your table scans down. If you kno=
w
that you'll mostly access just last month's data, partition on year+month.

YMMV.


On Mon, Feb 22, 2010 at 11:23 PM, Jerry Schwartz m
> wrote:

> I=92d like to know your opinions about partitioning the following table.
> Here=92s the relevant snippet:
>
>
>
> Create Table: CREATE TABLE `prod_price` (
>
> `prod_price_id` varchar(15) NOT NULL DEFAULT '',
>
> `prod_id` varchar(15) DEFAULT NULL,
>
> =85
>
> PRIMARY KEY (`prod_price_id`),
>
> KEY `prod_id` (`prod_id`)
>
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8
>
>
>
> Here=92s the deal. The primary key, `prod_price_id`, is rarely used. Pric=
es,
> as you might expect, are fetched by `prod_id`. Both keys are randomly
> generated strings. (Before you ask, I am not a mental health professiona=
l
> and am therefore not qualified to judge my predecessor.)
>
>
>
> How could I partition this table in a useful way?
>
>
>
> Regards,
>
>
>
> Jerry Schwartz
>
> The Infoshop by Global Information Incorporated
>
> 195 Farmington Ave.
>
> Farmington, CT 06032
>
>
>
> 860.674.8796 / FAX: 860.674.8341
>
>
>
> www.the-infoshop.com
>
>
>
>


--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0050450163335efddb048042564d--

RE: Partitioning

Hi Jerry,

I guess modification of the table is needed! What are you trying to achieve=
by partitioning?

If the primary key is rarely used then maybe adding another column with a n=
umeric value based on `prod_id` and adding that column to the primary key w=
ould work and at least let you do some hash partitioning to distribute data=
, may take some time to run if the table is large but something like this m=
ay work....

ALTER TABLE `prod_price` ADD COLUMN `partition_key` int unsigned;

ALTER TABLE `prod_price` DROP primary key;

UPDATE `prod_price` set `partition_key` =3D ASCII(prod_id);

ALTER TABLE `prod_price` add primary key(prod_price_id,partition_key);

ALTER TABLE `prod_price` PARTITION BY HASH(partition_key) PARTITIONS 4;

Just an rough idea based on me not knowing anything about your data and onl=
y a little about partitioning.

Be very interested to hear how you eventually overcome this issue so please=
do let me know what you decide.

Regards

John Daisley

==================
John Daisley
Certified MySQL DBA / Developer
IBM Cognos BI Developer

Tel: +44(0)1283 537111
Mobile: +44 (0)7819 621621
Email: john@butterflysystems.co.uk

==================

Sent via HP IPAQ mobile device.





-----Original Message-----=0A=
From: Jerry Schwartz
Sent: Monday, February 22, 2010 10:51 PM
To: mysql@lists.mysql.com
Subject: Partitioning

I'd like to know your opinions about partitioning the following table. Here=
's the relevant snippet:

=A0

Create Table: CREATE TABLE `prod_price` (

=A0 `prod_price_id` varchar(15) NOT NULL DEFAULT '',

=A0 `prod_id` varchar(15) DEFAULT NULL,

.

=A0 PRIMARY KEY (`prod_price_id`),

=A0 KEY `prod_id` (`prod_id`)

) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8

=A0

Here's the deal. The primary key, `prod_price_id`, is rarely used. Prices,=
as you might expect, are fetched by `prod_id`. Both keys are randomly=A0 g=
enerated strings. (Before you ask, I am not a mental health professional an=
d am therefore not qualified to judge my predecessor.)

=A0

How could I partition this table in a useful way?

=A0

Regards,

=A0

Jerry Schwartz

The Infoshop by Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=A0

860.674.8796 / FAX: 860.674.8341

=A0

=A0 www.the-infoshop.com

=A0



--
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: Partitioning

>-----Original Message-----
>From: John Daisley [mailto:mg_sv_r@hotmail.com]
>Sent: Tuesday, February 23, 2010 6:07 AM
>To: jschwartz@the-infoshop.com ; mysql@lists.mysql.com
>Subject: RE: Partitioning
>
>Hi Jerry,
>
>I guess modification of the table is needed! What are you trying to achieve
>by
>partitioning?
>
[JS] I was trying to get a feel for how partitioning would work, that's all. I
have no real need for partitioning. My tables are small, by most standards,
and the cardinality is excellent.

>If the primary key is rarely used then maybe adding another column with a
>numeric value based on `prod_id` and adding that column to the primary key
>would work and at least let you do some hash partitioning to distribute data,
>may take some time to run if the table is large but something like this may
>work....
>
[JS] I'd dearly love to ditch this whole key structure, but it would require a
lot of work for a relatively small investment.

Thanks.

Regards,

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

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com



>ALTER TABLE `prod_price` ADD COLUMN `partition_key` int unsigned;
>
>ALTER TABLE `prod_price` DROP primary key;
>
>UPDATE `prod_price` set `partition_key` = ASCII(prod_id);
>
>ALTER TABLE `prod_price` add primary key(prod_price_id,partition_key);
>
>ALTER TABLE `prod_price` PARTITION BY HASH(partition_key) PARTITIONS 4;
>
>Just an rough idea based on me not knowing anything about your data and only
>a
>little about partitioning.
>
>Be very interested to hear how you eventually overcome this issue so please
>do
>let me know what you decide.
>
>Regards
>
>John Daisley
>
>==================
>John Daisley
>Certified MySQL DBA / Developer
>IBM Cognos BI Developer
>
>Tel: +44(0)1283 537111
>Mobile: +44 (0)7819 621621
>Email: john@butterflysystems.co.uk
>
>==================
>
>Sent via HP IPAQ mobile device.
>
>
>
>
>
>-----Original Message-----
>From: Jerry Schwartz
>Sent: Monday, February 22, 2010 10:51 PM
>To: mysql@lists.mysql.com
>Subject: Partitioning
>
>I'd like to know your opinions about partitioning the following table. Here's
>the relevant snippet:
>
>
>
> Create Table: CREATE TABLE `prod_price` (
>
> `prod_price_id` varchar(15) NOT NULL DEFAULT '',
>
> `prod_id` varchar(15) DEFAULT NULL,
>
> .
>
> PRIMARY KEY (`prod_price_id`),
>
> KEY `prod_id` (`prod_id`)
>
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
>
>
>
> Here's the deal. The primary key, `prod_price_id`, is rarely used. Prices,
> as
>you might expect, are fetched by `prod_id`. Both keys are randomly generated
>strings. (Before you ask, I am not a mental health professional and am
>therefore not qualified to judge my predecessor.)
>
>
>
> How could I partition this table in a useful way?
>
>
>
> Regards,
>
>
>
> Jerry Schwartz
>
> The Infoshop by Global Information Incorporated
>
> 195 Farmington Ave.
>
> Farmington, CT 06032
>
>
>
> 860.674.8796 / FAX: 860.674.8341
>
>
>
> www.the-infoshop.com
>
>
>





--
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: Partitioning

------=_NextPart_000_028A_01CAB48F.3783ADA0
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable

=20

From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan =
De Meersman
Sent: Tuesday, February 23, 2010 5:52 AM
To: Jerry Schwartz
Cc: MY SQL Mailing list
Subject: Re: Partitioning

=20

that's very much gonna depend on what your selects look like. For =
example, a low-cardinality but often-where'd field makes an interesting =
candidate, as such a partitioning will take the size of your table scans =
down. If you know that you'll mostly access just last month's data, =
partition on year+month.

YMMV.

[JS] This is a thought experiment. The cardinality is excellent, since a =
give product typically has one or two prices.

Thanks.

=20

Regards,

=20

Jerry Schwartz

The Infoshop by Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=20

860.674.8796 / FAX: 860.674.8341

=20

www.the-infoshop.com

=20

=20

On Mon, Feb 22, 2010 at 11:23 PM, Jerry Schwartz =
wrote:

Iâ€=99d like to know your opinions about partitioning the following =
table. Hereâ€=99s the relevant snippet:



Create Table: CREATE TABLE `prod_price` (

`prod_price_id` varchar(15) NOT NULL DEFAULT '',

`prod_id` varchar(15) DEFAULT NULL,

â€=A6

PRIMARY KEY (`prod_price_id`),

KEY `prod_id` (`prod_id`)

) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8



Hereâ€=99s the deal. The primary key, `prod_price_id`, is rarely =
used. Prices, as you might expect, are fetched by `prod_id`. Both keys =
are randomly generated strings. (Before you ask, I am not a mental =
health professional and am therefore not qualified to judge my =
predecessor.)



How could I partition this table in a useful way?



Regards,



Jerry Schwartz

The Infoshop by Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032



860.674.8796 / FAX: 860.674.8341



www.the-infoshop.com






--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel


------=_NextPart_000_028A_01CAB48F.3783ADA0--