Re: Partition by range on InnoDB engine table ---Rows evenly distributed among all partitions

[Mikael Ronström]

Hi,
The number of rows in a query which InnoDB delivers
isn't exact, so perform the select and you should
see all 4 rows and it isn't going to look in any other
partition p0 since it pruned away those from the query.

Rgrds Mikael



gunwant Walbe Wrote:
-------------------------------------------------------
> 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