i working on finalising site go live soon. process 1 million files per week , store information these files in multiple tables in database.
the main table have 10 records per file gain 10million records per week. table has 85 columns storing 1.6kib of data per row.
i'm worried having 85 columns, seems crazy i'm more worried joins if split data multiple tables... if end 4 tables of 20 odd columns , on 500,000,000 records in each of them them, won't joins take massive amounts of time?
the joins take place on 1 column (traceid) present in tables , indexed.
the hardware run on i7 6700, 32gb ram. table type innodb.
any advice appreciated!
thanks!
the answer depends on structure of data. 85 columns lot. inconvenient add 86th column. queries verbose. select *, when use troubleshooting, splat lot of stuff across screen , you'll have trouble interpreting it. (don't ask how know this. :-)
if every item of data process has 1 instance of 85 values, , they're standalone values, you've designed table appropriately.
if rows have subset of 85 values, should figure out subsets , create table each one.
for example, if you're describing apartments , columns have meanings these:
livingroom yes/no livingroom_sq_m decimal (7,1) diningroom yes/no diningroom_sq_m decimal (7,1) you may want apartments table , separate rooms table columns this.
room_id pk apt_id fk room_name text room_sq_m decimal (7,1) in example, if of columns are
cost_this_week cost_week_1 cost_week_2 cost_week_3 etc. you should consider normalizing information in separate table.
