If a table which will be heavily used has numerous fields, yet only a
handfull of them will be used heavily, would it make sense performance wise to split it?
Example
Table 1
Field 1
....
Field 100
Table 2
References Field 1 of table1
.....
Table n
References Field 1 of table 1
So table 1 basically will be referenced by many tables and most of the
time only a handfull of fields of table 1 are needed. Don't have exact
numbers, but let's say that more than 60% of queries to table 1 queries
only use 20 fields or less.
If I split Table 1 then the second table will basically be a 1 to 1 to
Table 1.
I have this simmilar scenario for two tables. One is close to 1 Million
records and the other is about 300,000 records.
Programming wise it is much easier to only have one table, but I am just
concerned about performance.
Most access to these tables will be indexed with some occassional
sequential scans. Number of concurrent users now is probably 10 or less.
Expect to grow to 20+ concurrent connections. Will this be more of an
issue if I had hundreds/thousands of users?