List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:February 23 2010 6:50pm
Subject:RE: Partitioning
View as plain text  
>-----Original Message-----
>From: John Daisley [mailto:mg_sv_r@stripped]
>Sent: Tuesday, February 23, 2010 6:07 AM
>To: jschwartz@stripped ; mysql@stripped
>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@stripped
>
>==================
>
>Sent via HP IPAQ mobile device.
>
>
>
>
>
>-----Original Message-----
>From: Jerry Schwartz <jschwartz@stripped>
>Sent: Monday, February 22, 2010 10:51 PM
>To: mysql@stripped
>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
>
>
>
>  <http://www.the-infoshop.com> www.the-infoshop.com
>
>
>




Thread
PartitioningJerry Schwartz22 Feb
  • Re: PartitioningJohan De Meersman23 Feb
    • RE: PartitioningJerry Schwartz23 Feb
RE: PartitioningJohn Daisley 23 Feb
  • RE: PartitioningJerry Schwartz23 Feb