SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Mar 2006
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Need Database Table Design Help from gurus. Please :)

    Hi,

    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?


    Second Question
    If this was your database design, would you be wary of creating a table with 33 fields? Is that obnoxious in size and complexity?

    Thank you for your advice!
    Matt

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •