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=gcdmg-mysql-2@m.gmane.org

RE: Fancy partitioning scheme

TXlTUUwgZG9lc24ndCBzdXBwb3J0IGR5bmFtaWMgZGlzdHJpYnV0aW9uIG9m IGRhdGEgYW1vbmcg
cGFydGl0aW9ucy4gIFRoZSB1c3VhbCBtZXRob2QgaXMgdG8gY3JlYXRlIGEg cGFydGl0aW9uIGZv
ciBlYWNoIGZpeGVkIGNodW5rIG9mIHRpbWUsIHN1Y2ggYXMgZm9yIGVhY2gg bW9udGgvd2Vlay9o
b3VyL2RheSBvciB3aGF0ZXZlciB0aW1lIHNsaWNlIGJyZWFrcyB5b3VyIGRh dGEgdXAgaW4gdGhl
IG1hbmFnZWFibGUgcGllY2VzLiAgIE5vdGUgdGhhdCBhIHZlcnkgbGFyZ2Ug bnVtYmVyIG9mIHBh
cnRpdGlvbnMgKCA+IDEwMDAgaXNuJ3QgcmVhbGx5IHJlY29tbWVuZGVkLikN Cg0KT3RoZXIgbm90
ZXM6DQpQZXJzb25hbGx5LCBJIGF2b2lkIHNjaGVtYS1sZXNzIGNvbnN0cnVj dGlvbnMgbGlrZSB0
aGlzLCBiZWNhdXNlIHRoZXkgYXJlIGhhcmQgdG8gd29yayB3aXRoLiAgU3Vy ZSB0aGV5J3JlIGZs
ZXhpYmxlLCBidXQgeW91IG9mdGVuIHBheSBhIHByaWNlIGluIHBlcmZvcm1h bmNlLg0KDQoxMDAg
bWlsbGlvbiByb3dzIGlzbid0IGFsbCB0aGF0IG11Y2ggd2l0aCB0aGUgcHJv cGVyIGluZGV4aW5n
LiAgSXQgcmVhbGx5IGRlcGVuZHMgb24geW91ciBxdWVyaWVzIGFuZCBhY2Nl c3MgcGF0dGVybnMu
DQoNCldoeSBub3QgdXNlIG15c3FsIGRhdGV0aW1lIG9yIHRpbWVzdGFtcCB0 eXBlPyAgU3Rvcmlu
ZyB1bml4IHRpbWVzdGFtcHMgYXMgaW50IG1lYW5zIHlvdSdyZSBnb2luZyB0 byBoYXZlIHRvIGNv
bnZlcnQgdmFsdWVzIHRvIHVzZSBteXNxbCdzIGRhdGUgZnVuY3Rpb25zLg0K DQoNClJlZ2FyZHMs
DQpHYXZpbiBUb3dleQ0KDQotLS0tLU9yaWdpbmFsIE1lc3NhZ2UtLS0tLQ0K RnJvbTogQnJ5YW4g
Q2FudHdlbGwgW21haWx0bzpiY2FudHdlbGxAZmlyZXNjb3BlLmNvbV0NClNl bnQ6IFdlZG5lc2Rh
eSwgSnVuZSAwMiwgMjAxMCAxMjozMCBQTQ0KVG86IG15c3FsQGxpc3RzLm15 c3FsLmNvbQ0KU3Vi
amVjdDogRmFuY3kgcGFydGl0aW9uaW5nIHNjaGVtZQ0KDQpQZXJoYXBzIHNv bWVvbmUgaGFzIGFs
cmVhZHkgYWNjb21wbGlzaGVkIHRoaXM6DQoNCkkgaGF2ZSBhIHNpbXBsZSB0 YWJsZSB3aXRoIDMg
Y29sdW1uczoNCm15dGFibGUoDQogIG15aWQgQklHSU5UKDIwKSBVTlNJR05F RCBOT1QgTlVMTCBE
RUZBVUxUIDAsDQogIG15dW5peHRpbWUgSU5UKDExKSBOT1QgTlVMTCBERUZB VUxUIDAsDQogIG15
dmFsdWUgQklHSU5UKDIwKSBVTlNJR05FRCBOT1QgTlVMTCBERUZBVUxUIDAN CikNCkl0IGlzIGNv
bGxlY3RpbmcgbWlsbGlvbnMgb2Ygcm93cy4NClRoZSBteXVuaXh0aW1lIGNv bHVtbiBpcyBhIHVu
aXggdGltZXN0YW1wIGNvbHVtbi4NCkknZCBsb3ZlIHRvIGtub3cgaWYgaXQg aXMgcG9zc2libGUg
dG8gcGFydGl0aW9uIHRoZSB0YWJsZSBzbyB0aGF0IHRoZQ0KcGFydGl0aW9u cyB3b3VsZCBiZSBz
b21ldGhpbmcgbGlrZToNCg0KcGFydGl0aW9uIEEgPSBldmVyeXRoaW5nIG9u ZSBkYXkgb3IgbGVz
cyBvbGQsDQpwYXJ0aXRpb24gQiA9IGV2ZXJ5dGhpbmcgNyBkYXlzIG9sZCBv ciBsZXNzLA0KcGFy
dGl0aW9uIEMgPSBldmVyeXRoaW5nIDMxIGRheXMgb2xkIG9yIGxlc3MsDQpw YXJ0aXRpb24gRCA9
IGV2ZXJ5dGhpbmcgb2xkZXIgdGhhbiAzMSBkYXlzLg0KDQpDYW4gcGFydGl0 aW9uaW5nIGJlIHRo
aXMgZHluYW1pYz8gSWYgbm90LCB3aGF0IHNvbHV0aW9uIGNvdWxkIGJlDQpz dWdnZXN0ZWQgdG8g
aGFuZGxlIGRvaW5nIGRhdGUgcmFuZ2UgcXVlcmllcyBvbiB0aGlzIHRhYmxl IHRoYXQgY2FuIGhh
dmUNCjEwJ3Mgb3IgMTAwJ3Mgb2YgbWlsbGlvbnMgb2Ygcm93cz8NCg0KDQoN Cg0KLS0NCk15U1FM
IEdlbmVyYWwgTWFpbGluZyBMaXN0DQpGb3IgbGlzdCBhcmNoaXZlczogaHR0 cDovL2xpc3RzLm15
c3FsLmNvbS9teXNxbA0KVG8gdW5zdWJzY3JpYmU6ICAgIGh0dHA6Ly9saXN0 cy5teXNxbC5jb20v
bXlzcWw/dW5zdWI9Z3Rvd2V5QGZmbi5jb20NCg0KDQpUaGlzIG1lc3NhZ2Ug Y29udGFpbnMgY29u
ZmlkZW50aWFsIGluZm9ybWF0aW9uIGFuZCBpcyBpbnRlbmRlZCBvbmx5IGZv ciB0aGUgaW5kaXZp
ZHVhbCBuYW1lZC4gIElmIHlvdSBhcmUgbm90IHRoZSBuYW1lZCBhZGRyZXNz ZWUsIHlvdSBhcmUg
bm90aWZpZWQgdGhhdCByZXZpZXdpbmcsIGRpc3NlbWluYXRpbmcsIGRpc2Ns b3NpbmcsIGNvcHlp
bmcgb3IgZGlzdHJpYnV0aW5nIHRoaXMgZS1tYWlsIGlzIHN0cmljdGx5IHBy b2hpYml0ZWQuICBQ
bGVhc2Ugbm90aWZ5IHRoZSBzZW5kZXIgaW1tZWRpYXRlbHkgYnkgZS1tYWls IGlmIHlvdSBoYXZl
IHJlY2VpdmVkIHRoaXMgZS1tYWlsIGJ5IG1pc3Rha2UgYW5kIGRlbGV0ZSB0 aGlzIGUtbWFpbCBm
cm9tIHlvdXIgc3lzdGVtLiBFLW1haWwgdHJhbnNtaXNzaW9uIGNhbm5vdCBi ZSBndWFyYW50ZWVk
IHRvIGJlIHNlY3VyZSBvciBlcnJvci1mcmVlIGFzIGluZm9ybWF0aW9uIGNv dWxkIGJlIGludGVy
Y2VwdGVkLCBjb3JydXB0ZWQsIGxvc3QsIGRlc3Ryb3llZCwgYXJyaXZlIGxh dGUgb3IgaW5jb21w
bGV0ZSwgb3IgY29udGFpbiB2aXJ1c2VzLiBUaGUgc2VuZGVyIHRoZXJlZm9y ZSBkb2VzIG5vdCBh
Y2NlcHQgbGlhYmlsaXR5IGZvciBhbnkgbG9zcyBvciBkYW1hZ2UgY2F1c2Vk IGJ5IHZpcnVzZXMg
b3IgZXJyb3JzIG9yIG9taXNzaW9ucyBpbiB0aGUgY29udGVudHMgb2YgdGhp cyBtZXNzYWdlLCB3
aGljaCBhcmlzZSBhcyBhIHJlc3VsdCBvZiBlLW1haWwgdHJhbnNtaXNzaW9u LiBbRnJpZW5kRmlu
ZGVyIE5ldHdvcmtzLCBJbmMuLCAyMjAgSHVtYm9sdCBjb3VydCwgU3Vubnl2 YWxlLCBDQSA5NDA4
OSwgVVNBLCBGcmllbmRGaW5kZXIuY29tDQo=