List:General Discussion« Previous MessageNext Message »
From:John Daisley  Date:February 23 2010 11:07am
Subject:RE: Partitioning
View as plain text  
Hi Jerry,

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

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....

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.


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


 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`)



 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


 How could I partition this table in a useful way?




 Jerry Schwartz

 The Infoshop by Global Information Incorporated

 195 Farmington Ave.

 Farmington, CT 06032


 860.674.8796 / FAX: 860.674.8341




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