MySQL Forums
Forum List  »  Partitioning

Re: partition by range on decimal type
Posted by: Rick James
Date: November 21, 2012 10:39AM

The problem is here:
RANGE (floor(starttime))
Partition pruning is not smart enough to understand the characteristics of arbitrary functions. This one should be easy, since it is monotonic.

Bite the bullet. Change to one of the few datatypes that PARTITION understands. When partitioning on hour, I have used MEDIUMINT (only 3 bytes), and pre-calculated it via
hr = UNIX_TIMESTAMP(ts)/3600
By "precompute", I mean that I added the `hr` field and filled it in as I was inserting in the table. Then I did
PARTITION BY RANGE(hr)
from2012111100 VALUES LESS THAN (375729),
from2012111101 VALUES LESS THAN (375730),
...
future VALUES LESS THAN MAXVALUE
Note that I named the partitions in a useful way:
from2012111101 has the 1am values, and it ends at 2am:
SELECT FLOOR(UNIX_TIMESTAMP('2012-11-11 02:00:00')/3600) = 375730

A side note: There is almost no reason to have the secondary key here:
add PRIMARY KEY (`bts_gid`,`starttime`),
add KEY `bts_gid` (`bts_gid`),
since the PRIMARY KEY handles any lookups by bts_gid. Recommend removing the key.

decimal(14,0) takes 7 bytes.
decimal(20,0) takes 9 bytes.

Since you have a table big enough to consider partitioning, the table is big. So you should work hard at shrinking the fields as much as practical.

You have not said why you think PARTITIONing might be beneficial.
* Will you be purging "old" data? DROP PARTITION is excellent for this.
* Will all your SELECTs include a WHERE clause on the datetime? (Of course, with my suggestion, the conversion needs to be done in the WHERE clause. Suggest creating a Stored Function.)

More notes:
http://mysql.rjweb.org/doc.php/partitionmaint

Options: ReplyQuote


Subject
Views
Written By
Posted
5936
November 20, 2012 07:15AM
Re: partition by range on decimal type
2485
November 21, 2012 10:39AM
1903
November 22, 2012 02:26AM
2015
November 22, 2012 11:02PM
2015
November 23, 2012 12:45AM
2188
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.