SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    CSS & JS/DOM Adept bronze trophy
    Join Date
    Mar 2005
    Location
    USA
    Posts
    5,482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Would using a 2nd table be more efficient?

    For example, let's say that I have a table with 100 rows and 15 columns. I need to add 3 more columns, but maybe only about 30% of the rows will actually have data in those 3 columns.

    Would it be more efficient to use a second table of 4 or 5 columns instead of adding 3 columns to the first table?
    We miss you, Dan Schulz.
    Learn CSS. | X/HTML Validator | CSS validator
    Dynamic Site Solutions
    Code for Firefox, Chrome, Safari, & Opera, then add fixes for IE, not vice versa.

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

  3. #3
    SitePoint Zealot ShytKicka's Avatar
    Join Date
    Aug 2004
    Location
    New York
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No. The performance hit is mostly associated with reading the row rather than the data within when selecting. It's better to store it in 1 table rather than 2. But, if it is something like an authentication which is checked on each page, and it's between having a fixed table length rather than dynamic, than yes 2 tables would be better, but other than that no.

  4. #4
    CSS & JS/DOM Adept bronze trophy
    Join Date
    Mar 2005
    Location
    USA
    Posts
    5,482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your replies.

    I forgot to mention that the columns types will be some combination of int, varchar, and datetime.

    Quote Originally Posted by ShytKicka
    It's better to store it in 1 table rather than 2. But, if it is something like an authentication which is checked on each page, and it's between having a fixed table length rather than dynamic, than yes 2 tables would be better, but other than that no.
    Would you please explain that further?

    The table is being used to store user information, which is accessed on almost every page request.
    We miss you, Dan Schulz.
    Learn CSS. | X/HTML Validator | CSS validator
    Dynamic Site Solutions
    Code for Firefox, Chrome, Safari, & Opera, then add fixes for IE, not vice versa.

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it's (marginally) faster to retrieve a fixed-length row than a variable-length row. if the table you're adding to already has any variable-length columns in it, don't worry about creating a second table. variable length types include, but are not limited to, varcahr, text, and blob.

    or, if you need any of the columns you are adding frequently, then definitely don't put them in a second table.


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
  •