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

Regards

John Daisley

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

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

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

Sent via HP IPAQ mobile device.





-----Original Message-----
From: Jerry Schwartz <jschwa...@the-infoshop.com>
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

  

  <http://www.the-infoshop.com> www.the-infoshop.com

  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=arch...@jab.org

Reply via email to