MySQL Forums
Forum List  »  Partitioning

partition by range on decimal type
Posted by: Greg K
Date: November 20, 2012 07:15AM

hi experts,

I need your help in getting my partitioning working.
I have a problem with paritioning on DECIMAL column (in fact this should be DATE but I have no chance to change it).
So I use floor() to return integer. Column 'starttime' used in partitioning is used in primary key, so the requirement is met.

this is my code:
===============

mysql> desc bts_hour;
+-----------------------------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------------------------+---------------+------+-----+---------+-------+
| bts_gid | decimal(20,0) | NO | PRI | NULL | |
| starttime | decimal(14,0) | NO | PRI | NULL | | <-------- this is it
| bsc_gid | decimal(20,0) | NO | | NULL | |
| BSC_NAME | varchar(80) | YES | | NULL | |
| BTS_NAME | varchar(80) | YES | MUL | NULL | |
...

#added index
alter table test.bts_hour
add PRIMARY KEY (`bts_gid`,`starttime`),
add KEY `starttime` (`starttime`),
add KEY `bts_gid` (`bts_gid`),
add KEY `bts_name` (`BTS_NAME`);

select count(*) from test.bts_hour;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+

select min(starttime), max(starttime) from test.bts_hour;
+----------------+----------------+
| min(starttime) | max(starttime) |
+----------------+----------------+
| 2011033000 | 2011063018 |
+----------------+----------------+

# add partitiong
alter table test.bts_hour
partition by RANGE (floor(starttime)) (
partition p00 values less than (2011040100),
partition p01 values less than (2011050100),
partition p02 values less than (MAXVALUE)
);

#check ditribution of data among partitions
select partition_name part, partition_expression expr, partition_description val, partition_method meth, table_rows
from information_schema.partitions
where table_name = 'bts_hour'
and TABLE_SCHEMA = 'test';

+------+------------------+------------+-------+------------+
| part | expr | val | meth | table_rows |
+------+------------------+------------+-------+------------+
| p00 | floor(starttime) | 2011040100 | RANGE | 20352 |
| p01 | floor(starttime) | 2011050100 | RANGE | 297084 |
| p02 | floor(starttime) | MAXVALUE | RANGE | 682564 |
+------+------------------+------------+-------+------------+

===================

now, when I run 'explain', all the partitions are used instead of p01 that has the set of values I want!

explain partitions
SELECT bts_name, str_to_date(convert(STARTTIME, CHAR(10)), '%Y%m%d%H') as Date
FROM test.bts_hour
WHERE STARTTIME between 2011040200 and 2011040523
ORDER BY bts_name, starttime
\G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bts_hour
partitions: p00,p01,p02 <--------- here it is, instead of p01 only
type: range
possible_keys: starttime,starttime_bts_gid,starttime_bts_name
key: starttime_bts_name
key_len: 7
ref: NULL
rows: 26021
Extra: Using where; Using index; Using filesort
1 row in set (0.00 sec)


What's wrong with this approach?

Options: ReplyQuote


Subject
Views
Written By
Posted
partition by range on decimal type
3408
November 20, 2012 07:15AM
1564
November 21, 2012 10:39AM
1293
November 22, 2012 02:26AM
1372
November 22, 2012 11:02PM
1500
November 23, 2012 12:45AM
1604
November 23, 2012 09:03PM


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.