MySQL Forums
Forum List  »  Partitioning

Re: Partition based on dynamic customer list
Posted by: Todd Williams
Date: October 09, 2008 02:14PM

Thank you, but, if i understand hashes and partitioning correctly, that would essentially randomize the physical distribution of my customer data. That would make all queries against a single customer (select * from Customer 001 where timestamp > '2008-01-01') slower than without partitioning.

In case I explained myself poorly, here is a more tabular explanation
Conceptual Data Sample
event_id customer_id event_date {event data columns}
00001 00001 10/8/07 1:00 Stuff Here
00002 00002 10/8/07 1:02 Stuff Here
00003 00001 10/8/07 1:04 Stuff Here

00004 00001 10/8/08 1:06 Stuff Here
00005 00002 10/8/08 1:08 Stuff Here
00006 00003 10/8/08 1:10 Stuff Here
00007 00001 10/8/08 1:12 Stuff Here

Physical Storage
Partition 1 (Customer 00001 - 2007)
event_id customer_id event_date {event data columns}
00001 00001 10/8/07 1:00 Stuff Here
00003 00001 10/8/07 1:04 Stuff Here

Partition 2 (Customer 00001 - 2008)
event_id customer_id event_date {event data columns}
00004 00001 10/8/08 1:06 Stuff Here
00007 00001 10/8/08 1:12 Stuff Here

Partition 3 (Customer 00002 - 2007)

Partition 4 (Customer 00002 - 2008)

...

Partition N (Customer 01234 - 2007)
Partition N+1 (Customer 01234 - 2008)
Partition N+2 (Customer 01234 - 2009)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Partition based on dynamic customer list
2579
October 09, 2008 02:14PM


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.