MySQL Forums
Forum List  »  Partitioning

Re: PARTITION BY RANGE ,problem for NULL values
Posted by: Rick James
Date: January 23, 2011 12:08PM

PARTITION p0 VALUES LESS THAN (to_days('0000-00-00')),
PARTITION p1 VALUES LESS THAN (to_days('2009-03-31')),
PARTITION p2 VALUES LESS THAN (to_days('2009-04-30')),
...
-->
PARTITION p0000 VALUES LESS THAN (to_days('2009-03-01')), -- Not 0000
PARTITION p0903 VALUES LESS THAN (to_days('2009-04-01')), -- Start of month (as Mattias suggests)
PARTITION p0904 VALUES LESS THAN (to_days('2009-05-01')),
...

Note suggested names -- matching the month's date that it contains.

Your original code would have put rows for the last day of the month in the wrong partition.

I assume you are using PARTITION because of the huge size of the table? Let's do some other things to shrink the table (and fix other bugs)...

`uid` char(255) NOT NULL,
`lid` char(255) NOT NULL,
What kind of IDs are these? If they are UUIDs, (36) suffices.

`ip_address` char(15) NOT NULL,
IPv6 is rolling out now. VARBINARY(39) would allow for such.

`user_agent` char(255) DEFAULT NULL,
1. Don't use CHAR for variable length data; it wastes a lot of space.
2. Normalize this (to further condense space)

`referrer` char(255) DEFAULT NULL,
VARCHAR; maybe normalize (depends on cardinality)

`country` char(255) NOT NULL,
Simply use the standard CHAR(2) abbreviations for countries.

latin1
Are you restricting your data to Western sites?

These schema changes will shrink the table by a factor of (perhaps) 4.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: PARTITION BY RANGE ,problem for NULL values
2135
January 23, 2011 12:08PM


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.