On Fri, 2003-08-01 at 11:08, Francisco Reyes wrote: > 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.
Do all 100 fields *really* all refer to the same *one* entity, with no repeating values, etc? If not, then good design says to split the table. Also, if it's a high-activity table, but you only rarely need fields 60-90, then splitting them out to their own table might be useful (especially if some of those fields are large *CHAR or TEXT). > 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? > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: [EMAIL PROTECTED] | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+ ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly