How to design? Big tables with huge number of fields
Posted by:
chi ding
Date: January 31, 2005 12:28PM
Hi there
I'm new to this forum. Glad to meet everybody.
I now have a question. Here is my situation. I'm designing a database for huge scientific data. A typical data file contains the following fields
year, time, location, 300 variables in 6 groups, and 300 flags for the variables. People will search data on year, time, location and the flags. Total Anual data is about 80GB big.
Perioic write/modify operations will be executed on the database. It has to be done in time.
As for the data query/retrieval efficiency, it can be slower in the sense that the person who orders the data can wait for the data to be available.
I have the following questions:
1) what's the limit of max number of fields in one mysql table? if I put the fields into a table, then I need about 600 fields.
2) Should I create a table to hold the data for a typical data file?
In this way, I'll make the table hold year, time,localtion, 300 variables, and 300 flags. Since people need to query on flags ( about 300 flags), do I need to create indexes on these flags?
My concern is that the indexes could be very big, therefore drop the query efficiency and the efficiency when insert/modify data.
3) Or should I create 6 seperate tables for the 6 groups of variables? Each of the tables holds the group of variables and their flags.
In this way, for each table, the indexes will not be that big. In addition, the number of fields in each table is smaller, resulting in smaller rows.
Can anybody give me your suggestions?
Thanks a lot, Chi