>-----Original Message----- >From: John Daisley [mailto:mg_s...@hotmail.com] >Sent: Tuesday, February 23, 2010 6:07 AM >To: jschwa...@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: 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
