MySQL Forums
Forum List  »  Partitioning

Re: How to partition my table
Posted by: Rick James
Date: March 15, 2009 01:09PM

I would expect you to partition on day or hour. And be ready to drop old partitions to purge data. See
http://dev.mysql.com/doc/refman/5.1/en/partitioning-range.html

Since you are into "data warehousing", let me give you some tips...

Shrink the data! I repeat, shrink the data!
* Normalize where reasonable.
* Consider inet_aton() for storing IP addresses in a 4-byte INT UNSIGNED instead of a 8- to 16-byte VARCHAR.
* What "size" needs two decimal places? Consider a 4-byte FLOAT instead.
* CHAR(15) _always_ consumes 15 bytes in the table. VARCHAR(15) consumes 1 to 16 bytes.
* TIMESTAMP is (used to be?) 4 bytes; DATETIME is 8 bytes.

Consider 'summary' tables. This will make reports soooooo much faster.

Your PRIMARY KEY assumes that no one can login twice in one second? And you probably want it flipped: (user, logdate)

If you get serious about summary table, recommend just an AUTO_INCREMENT on this table, not the PK you have.

Options: ReplyQuote


Subject
Views
Written By
Posted
10170
March 15, 2009 09:36AM
Re: How to partition my table
4667
March 15, 2009 01:09PM
3942
March 17, 2009 10:35PM
3553
March 18, 2009 12:16AM
3387
March 18, 2009 09:12PM
3370
March 18, 2009 10:19PM
3708
March 19, 2009 05:18AM
3649
March 19, 2009 05:34AM
3365
April 06, 2009 08:08AM
3861
April 07, 2009 09:44PM
2706
April 08, 2009 08:00AM


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.