From: Gavin Towey (gtoweyffn.com)
Date: Wed Jun 02 2010 - 14:49:50 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

MySQL doesn't support dynamic distribution of data among partitions. The usual method is to create a partition for each fixed chunk of time, such as for each month/week/hour/day or whatever time slice breaks your data up in the manageable pieces. Note that a very large number of partitions ( > 1000 isn't really recommended.)

Other notes:
Personally, I avoid schema-less constructions like this, because they are hard to work with. Sure they're flexible, but you often pay a price in performance.

100 million rows isn't all that much with the proper indexing. It really depends on your queries and access patterns.

Why not use mysql datetime or timestamp type? Storing unix timestamps as int means you're going to have to convert values to use mysql's date functions.


Regards,
Gavin Towey

-----Original Message-----
From: Bryan Cantwell [mailto:bcantwellfirescope.com]
Sent: Wednesday, June 02, 2010 12:30 PM
To: mysqllists.mysql.com
Subject: Fancy partitioning scheme

Perhaps someone has already accomplished this:

I have a simple table with 3 columns:
mytable(
  myid BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  myunixtime INT(11) NOT NULL DEFAULT 0,
  myvalue BIGINT(20) UNSIGNED NOT NULL DEFAULT 0
)
It is collecting millions of rows.
The myunixtime column is a unix timestamp column.
I'd love to know if it is possible to partition the table so that the
partitions would be something like:

partition A = everything one day or less old,
partition B = everything 7 days old or less,
partition C = everything 31 days old or less,
partition D = everything older than 31 days.

Can partitioning be this dynamic? If not, what solution could be
suggested to handle doing date range queries on this table that can have
10's or 100's of millions of rows?




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gtoweyffn.com


This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]