Unanswered: Need Database Table Design Help from gurus. Please :)
I need some advice from database gurus.
I have some coders creating a mysql/php based message board for me from scratch. We have a single table that contains all individual posts.
Now for the question.
Currently the post table has 16 fields. I have suggested a needed change that would involve adding 17 more fields to the post table structure.
My coders tell me adding 17 fields will hurt performance as the table grows bigger and bigger. He says it is better to have a second table that has an entry for every individual post with these new fields of data. Then when we need that extra data we simply do a query that joins the tables.
I need you to give me some sense of what to do.
I am greatly concerned about speed and performance. The reason I added these fields to the table is to avoid doing live queries for the data. I want to simply be able to read the table records and get the data we need. How much will a big join query cost me in performance?
So first question, which of these two queries will be faster:
A query on a table with 33 fields vs
A join query on a two tables, one with 16 fields one with 18 fields? (the second table will need a post_id field)A ssume there are about 100,000 unique records in both tables.
Does your answer change is there are 1,000,000 unique records in both tables?
If this was your database design, would you be wary of creating a table with 33 fields? Is that obnoxious in size and complexity?
depends on whether every row in the first table has a matching row in the second, and whether you always need both rows in every query or only in certain queries, and how often those certain queries are run compared to the queries which need only the first table
there's nothing wrong with 33 columns in a table, it's not the number of columns which governs performance, but how wide those columns are, how many rows there are, how well the indexes are defined, and how efficiently the queries use the indexes