MySQL Forums
Forum List  »  Performance

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

Options: ReplyQuote

Written By
How to design? Big tables with huge number of fields
January 31, 2005 12:28PM

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.