Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    May 2011
    Location
    NYC
    Posts
    28
    Thanks
    5
    Thanked 0 Times in 0 Posts

    One table or seperate tables

    A little unsure here.

    I'm adding the ability to post user comments on images, blogs, articles, profiles and am wondering if its best to create ONE comment table and somehow tag what the comment belongs to, or if its just easier to create one table for EACH type of comment (art_comments, blog_comments, etc).

    My gut tells me having a seperate one for each type is the way to go! Of course this rookie gut might be wrong as usual

    PS currently I have set up seperate tables, and it works just fine, but I was thinking of maybe using an addComment class in PHP vs having addImgComment/addBlogComment/etc functions, and just pass a parameter to it specifying what the comment is meant for.

    Double PS, this is on an offline site, just a personal task to learn some... And when done make it live

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    Recent response to same question:
    http://www.codingforums.com/showthread.php?t=229114

  • Users who have thanked Old Pedant for this post:

    mrdemin (06-22-2011)

  • #3
    New Coder
    Join Date
    May 2011
    Location
    NYC
    Posts
    28
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hmm I should've went through an extra page of topics I guess haha.

    You mention there should be no bottleneck with the single table design, looking ahead I have to ask (being that its ratings in my case, and will get pretty heavy), are you suggesting there will be no real slowdown even if I will be counting, and averaging rows with a WHERE clause (to specify what I'm counting/averaging a rating for)?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    It's hard to say there will be *NO* slowdown, but if you add an index onto the field(s) that your WHERE clause is based on, the slowdown will be minimal.

    There's no way to give a definitive answer for all situations, so treat what was written as general guidelines. When in doubt, learn to use MySQLs EXPLAIN feature which will help find bottlenecks, if they exist.

  • #5
    New Coder
    Join Date
    Aug 2011
    Location
    Sydney
    Posts
    26
    Thanks
    2
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by mrdemin View Post
    Hmm I should've went through an extra page of topics I guess haha.

    You mention there should be no bottleneck with the single table design, looking ahead I have to ask (being that its ratings in my case, and will get pretty heavy), are you suggesting there will be no real slowdown even if I will be counting, and averaging rows with a WHERE clause (to specify what I'm counting/averaging a rating for)?
    There definitely will be a slowdown if you are counting and averaging rows. As suggested earlier, indexing definitely helps a fair bit.

    I recently worked on a really large database (several thousands of queries / minute). The database had no indexes set up (or very messy indexes) and after creating "smart" indexes, we managed to reduce their server load on SQL by up to 30%. Query times also decreased.

    Just goes to show that indexing really helps significantly.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    I'm surprised it was only 30%. I would have expected more like 90% to 95% in typical situations. Yours must have been really complicated.

  • #7
    New Coder
    Join Date
    Aug 2011
    Location
    Sydney
    Posts
    26
    Thanks
    2
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Old Pedant View Post
    I'm surprised it was only 30%. I would have expected more like 90% to 95% in typical situations. Yours must have been really complicated.
    They already had indexes setup so their database wasn't absolutely horrible - it was just extremely messy.

    30% was a great improvement over the previous 'optimization' they paid for. We are going to restructure their entire db in the coming months. It's basically a company that grew too quickly and whoever made their website and did their database structures didn't care about scalability.

    Oh well should be fun.


  •  

    Posting Permissions

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