Thanks Joe, I have edit the script and added primary key (CustomerID, TimeCreated ) and did partitionong on (TimeCreated) by range. I inserted some records according to partition year . How would i know which partition contains what record? (as i am using Windows Xp development machine. I can't specify data directory for perticular partition ) How can i see individual records from particular partition? Regards Gunwant
joe <[EMAIL PROTECTED]> wrote: Try it and see where the data goes. It amazing how much you learn by doing it. The first problem that you will see is the PK and unique keys in partitioning have to have the partition key as part of the index. So your PK would have to be CustomerID, TimeCreated -----Original Message----- From: gunwant walbe [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 27, 2007 5:23 AM To: mysql@lists.mysql.com Subject: Mysql Table partitioning hi all, I want to know some information about table partitioning. consider following script create table `customers`( `CustomerID` varchar(40) not null, `TimeCreated` DATETIME not null default 0, `TimeModified` datetime not null default 0, `isActive` tinyint not null default 'V', `CompanyName` varchar(100) not null, `CompanyDesc` varchar(200), `FirstName` varchar(20) not null, `MiddleName` varchar(20) , `LastName` varchar(20) not null, `FullName` varchar(30) not null, `BillAddressAddr` varchar(300) not null, `BillAddressCity` varchar(30) not null, `BillAddressState` varchar(30) not null, `BillAddressPostalCode` varchar(10) not null, `BillAddressCountry` varchar(20) not null, `shipaddress` varchar(300) not null, `ShipAddressCity` varchar(20) not null, `ShipAddressState` varchar(20) not null, `ShipAddressCounty` varchar(30) not null, `ShipAddressPostalCode` varchar(10)not null, `Phone` varchar(20) not null, `altPhone` varchar(20) not null default '0', `Fax` varchar(20) not null default '0', `email` varchar(50) not null, `CreditCardInfoCreditCardNumber` VARCHAR (25) not null default '0', `CreditCardInfoExpirationMonth` INTEGER not null default 0, `CreditCardInfoExpirationYear` INTEGER not null default 0, `CreditCardInfoNameOnCard` varchar(50) not null, `CreditCardInfoCreditCardAddress` varchar(50)not null, `CreditCardInfoCreditCardPostalCode` varchar(10)not null, `Notes` varchar(4000) not null default ' ' , PRIMARY KEY(`CustomerID`) )ENGINE InnoDB PARTITION BY RANGE ( YEAR(TimeCreated) ) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE ); Say i inserterd some rows having TimeCreated=1990,1992, 1994,1998 .........like Now row will go to respective partition (for 1990 to partition p0) Now updated the row with Time created year to 1992, I want to know , wheather this row will remain in partition p0 or move to partition p1? Regards Gunwant --------------------------------- Get easy, one-click access to your favorites. Make Yahoo! your homepage. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00 AM No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --------------------------------- Get easy, one-click access to your favorites. Make Yahoo! your homepage.
