MySQL Forums
Forum List  »  Partitioning

Re: Partition by range on InnoDB engine table ---Rows evenly distributed among all partitions
Posted by: Mikael Ronström
Date: November 28, 2007 03:39PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Partition by range on InnoDB engine table ---Rows evenly distributed among all partitions
3476
November 28, 2007 03:39PM


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.