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.
Are you restricting your data to Western sites?
These schema changes will shrink the table by a factor of (perhaps) 4.