MySQL Forums
Forum List  »  Partitioning

Partition by range on InnoDB engine table ---Rows evenly distributed among all partitions
Posted by: gunwant Walbe
Date: November 28, 2007 03:20AM

Hi all,

I created a table using partition clause

create table script :
CREATE TABLE `PARTITIONTEST`(
`PartitionID` INTEGER not null AUTO_INCREMENT,
`PartName` VARCHAR(10) not null default 'p',
`PartSize` bigint not null ,

PRIMARY KEY(`PartitionID`)
)Engine=InnoDB

PARTITION BY RANGE (PartitionID) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (15),
PARTITION p3 VALUES LESS THAN (20),
PARTITION p4 VALUES LESS THAN (25)

);


Now i inserted 10 rows in table having PartitionID 1 to 11.

According to range partition first four rows (having PartitionID 1 to 4 ) should go in partition p0 .
To check this i did a test, i fired following query from MySql command prompt
Query:

mysql> EXPLAIN PARTITIONS SELECT * FROM partitiontest where partitionid<5\G

the result was this.

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: partitiontest
partitions: p0
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 2 --------------- I was expecting No of rows should be 4 here
Extra: Using where
1 row in set (0.01 sec)


This was not the result what i expected , it should give 4 rows.
I think the inserted rows are evenly distributed among all partitions,
as each partition shows 2 rows as reasult of EXPLAIN PARTITION Query.

Can anybody please put alight on this?


Regards
Gunwant



Edited 2 time(s). Last edit at 11/28/2007 05:35AM by gunwant Walbe.

Options: ReplyQuote


Subject
Views
Written By
Posted
Partition by range on InnoDB engine table ---Rows evenly distributed among all partitions
7187
November 28, 2007 03:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.