RE: Mysql Table partitioning
Thanks Joe, Its really intersteing .. joe [EMAIL PROTECTED] wrote: Hope this helps Data is stored in the RANGE partition that the partition key falls into regardless if its an insert or update. One of the benefit of this is partition pruning. By specifing the partitioned field in a where cluase the optimizer know that it only needs to look into those partitions that fit into the range values of the partition key. Below is your table defintion, some inserts, and selects and explain plans with partitions showing the partition pruning and where the data is. create table `customers`( `CustomerID` varchar(40) not null, `TimeCreated` DATETIME not null default 0, PRIMARY KEY(`CustomerID`, `TimeCreated`) )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 ); insert into customers values (1,now()); insert into customers values (2,date_sub(now(),interval 5 year)); insert into customers values (3,date_sub(now(),interval 10 year)); insert into customers values (4,date_sub(now(),interval 15 year)); insert into customers values (5,date_sub(now(),interval 20 year)); mysql select * from customers; ++-+ | CustomerID | TimeCreated | ++-+ | 1 | 2007-11-28 08:12:48 | | 2 | 2002-11-28 08:13:08 | | 3 | 1997-11-28 08:13:09 | | 4 | 1992-11-28 08:13:09 | | 5 | 1987-11-28 08:13:09 | ++-+ analyze table customers; select table_name, partition_name, table_rows from information_schema.partitions where table_name = 'customers'; ++++ | table_name | partition_name | table_rows | ++++ | customers | NULL | 9 | | customers | p0 | 1 | | customers | p1 | 1 | | customers | p2 | 1 | | customers | p3 | 2 | ++++ mysql explain partitions select * from customers where timecreated = '1990-12-31'; ++-+---++---+---+--- --+-+--+--+--+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+---+--- --+-+--+--+--+ | 1 | SIMPLE | customers | p0 | index | NULL | PRIMARY | 50 | NULL | 2 | Using where; Using index | ++-+---++---+---+--- --+-+--+--+--+ mysql select * from customers where timecreated = '1990-12-31'; ++-+ | CustomerID | TimeCreated | ++-+ | 5 | 1987-11-28 08:13:09 | ++-+ --- mysql explain partitions select * from customers where timecreated between '1991-01-01' and '1995-12-31'; ++-+---++---+---+--- --+-+--+--+--+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+---+--- --+-+--+--+--+ | 1 | SIMPLE | customers | p1 | index | NULL | PRIMARY | 50 | NULL | 2 | Using where; Using index | ++-+---++---+---+--- --+-+--+--+--+ mysql select * from customers where timecreated between '1991-01-01' and '1995-12-31'; ++-+ | CustomerID | TimeCreated | ++-+ | 4 | 1992-11-28 08:13:09 | ++-+ mysql explain partitions select * from customers where timecreated = '1995-12-31'; ++-+---++---+---+--- --+-+--+--+--+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+---+--- --+-+--+--+--+ | 1 | SIMPLE | customers | p0,p1 | index | NULL | PRIMARY | 50 | NULL | 2 | Using where; Using index | ++-+---++---+---+--- --+-+--+--+--+ 1 row in set (0.00 sec) mysql select * from customers where timecreated = '1995-12-31'; ++-+ | CustomerID | TimeCreated | ++-+ | 4 | 1992-11-28 08:13:09 | | 5 | 1987-11-28 08:13:09 | ++-+ -Original Message- From: gunwant walbe [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 28, 2007 12:21 AM
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.
RE: Mysql Table partitioning
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.
