MySQL Forums :: Partitioning :: partition by range on decimal type


Advanced Search

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 3137 Greg K 11/20/2012 07:15AM
Re: partition by range on decimal type 1494 Rick James 11/21/2012 10:39AM
Re: partition by range on decimal type 1246 Greg K 11/22/2012 02:26AM
Re: partition by range on decimal type 1322 Rick James 11/22/2012 11:02PM
Re: partition by range on decimal type 1448 Greg K 11/23/2012 12:45AM
Re: partition by range on decimal type 1551 Rick James 11/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.