MySQL Forums
Forum List  »  Partitioning

Partitioning on data: Without storing data in table.
Posted by: Ijaz Rashid
Date: December 29, 2006 01:43AM

================================
QUESTION = CAN I USE PARTITIOINING ?
================================
I DON'T WANT TO STORE "POINTID" OR "DATE" INSIDE TABEL(to reduce storage size).
I WANT TO MARK THE PARTIOINS WITH "POINTID" AND CREATE SUB-PARTIOIN INSIDE PARTITION, TO BE MARKED WITH "DATE", WITHOUT STORING POINTID & DATE IN TABLE.



I'm having data 100,000 points data to be stored every Second as below, (All points data is recieved sequentially after every second )

Please let me know, how to define smallest storage for data below with possible efficient retrival time
================================
PointID DataTime Value
================================
1 2006-12-28 00:00:01 100
1 2006-12-28 00:00:02 101
1 2006-12-28 00:00:03 105
2 2006-12-28 00:00:01 150
2 2006-12-28 00:00:02 151
2 2006-12-28 00:00:03 155
.....
100,000 2006-12-28 00:00:01 180
100,000 2006-12-28 00:00:02 182
100,000 2006-12-28 00:00:03 194

=========
SOLUTION 1:
=========
If i define 100,000 tables (One for each PointID), I can save 3 bytes Bcz i don't have to save PointID for any record.


=========
Solution 2:
=========
I can generate new ID by combining 2 colums (PointID + Date = PointDateID). I have to generate 100,000 new IDs every day and save them in new table
================================
PointID Date PointDateID
================================
1 2006-12-28 1
2 2006-12-28 2
3 2006-12-28 3
.....
100,000 2006-12-28 100,000 (Note:Date Changed in rows below)

1 2006-12-29 100,001
2 2006-12-29 100,002
3 2006-12-29 100,003
.....
100,000 2006-12-29 200,000.......


==========================
PointDateID Time Value
==========================
1 00:00:01 101
1 00:00:02 105
1 00:00:03 199
2 00:00:01 131
2 00:00:02 135
2 00:00:03 149
.....
100,000 00:00:01 121
100,000 00:00:02 131
100,000 00:00:01 141 .....

Options: ReplyQuote


Subject
Views
Written By
Posted
Partitioning on data: Without storing data in table.
3132
December 29, 2006 01:43AM


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.