1. virtual pet mascot

    Virtual Pets

    Welcome to the Virtual Pet list forum!
    We're a virtual pets and sim games community and the largest forum in our industry. Game owners can use our forum to their advatange by promoting/advertising their games to our users and receive feedback/reviews from those members that try their games out. We support developers of all kinds from RPG developers, sim game developers to virtual pet site developers. We want to see more games in our industry come across more success and that's why we try our best to release as many guides/articles regarding online gaming development as we can.
    We're unlike other forums in our industry because we try our best to be there for our users no matter what and give them advice whenever they ask for it. Running an online game isn't an easy task, but with our many resources that we've built since 2011, you'll learn a lot from our online community. Please don't ever be afraid to ask a question on the forums because if you never ask, then you'll never receive an answer to your question and you can only improve by asking other users for help/advice.
    We have a lot of virtual pet sites' owners and sim game owners that are very frequent visitors on our online community. Along with artists, writers and programmers that come here looking for work and games to work for. Our members who are artists sell art in our art marketplace and programmers are allowed to sell their scripts in the programming marketplace. We also allow game owners to sell their game in our general marketplace
    In early 2011, we decided to make a comeback to the internet because in 2010, we had a minor setback and we had to re-launch without any content, but with a lot of time, patience, hardwork and effort, we've managed to rebuild the Virtual pet list community into something better than it was before. We actually registered our domain name on November 4th, 2004, but we were only a directory then and all we had was a basic comments system for users to post things on their favorite games..
    So, what are you waiting for, why don't you join today? If you have any problems figuring out our security code, then here's a hint, it has millions upon users and has been around since 1997.
    Our list of virtual pet sites and directory of sim games have a lot of different games listed, so we're pretty sure that you may find some games that suits your needs as a player of sim games or virtual pet sites.
    If you're still curious about a certain game that you'd like to play, then please check out some of our game reviews that were written by some of our great contributors and our interviews with respective virtual pet site owners, sim game owners, artists, writers and programmers that have worked in this industry.

  2. Check out our latest guide on how to grow your user base!
    Paladore - Boopets - Icepets - Corepets open beta
    Virtual Pet Directory
    Are you looking for new virtual pet sites or sim games to play?
    VPL is looking to hire 2-3 new moderators to help our flourishing community. If you're interested, then please see this thread VPL seeking moderators

Too many MySQL tables?

Discussion in 'Programming General' started by Gabby, Mar 3, 2013.

  1. Gabby

    Gabby Active Member Former VPL Staff VPL Member

    Reputations:
    205
    Joined:
    Apr 1, 2012
    Messages:
    7,784
    Likes Received:
    1,347
    Trophy Points:
    113
    Gender:
    Female
    Location:
    in a song, New York
    I was reading about databases with a ton of tables on Stack Overflow and I found that some people feel there is a performance impact when there is too many MySQL tables on a server. But in the same breath, it caused a huge debate with people who disagree. I'm just wondering if there's a proven answer? Maybe with trials or something?

    I know database normalization may result in many tables, which has me so confused. Of course, I know, normalizing all of your data helps the health of your database in the long run, but it also creates more MySQL tables, which some say cause a performance impact, so does anyone have a straight answer to this? XD
     
  2. Chevy

    Chevy Pet Game Owner Pet Game Owner Junior Programmer VPL Member

    Reputations:
    0
    Joined:
    Mar 25, 2011
    Messages:
    163
    Likes Received:
    18
    Trophy Points:
    18
    I would say make as few tables as you need, but with a pet site, it will be quite a few. Once you start putting rows in the wrong table (places that don't make sense) or make a table for site wide global fields (which I think is totally unnecessary, some might differ in that opinion) it gets really confusing.
     
  3. judda

    judda Bringer of Awesome Moderator Pet Game Owner Programming Moderator

    Reputations:
    18
    Joined:
    Jan 23, 2011
    Messages:
    1,548
    Likes Received:
    422
    Trophy Points:
    83
    The alternative is to have VARCHAR (text-based) lookups on your main tables (instead of INT lookups). The VARCHAR lookups will be way more expensive on a big fact table than any impact of too many tables in a MySQL database. People choose very interesting things to have holy wars over. This being one of them. I'm guessing that the people who are throwing out the facts about how too many tables will cause a performance hit are the same people who say all normalization is bad. Probably sponsored by the "anti-join order".

    The fact is, if you are storing the VARCHAR version of the text on each table, you will be duplicating a lot of information (the string value) which costs more (disk space wise, and index wise) than storing an integer value. Example:
    VARCHAR(50) => if full will take 51 bytes worth of disk/index space (per row) - actual size of VARCHAR fields is the length of content in the row + 1 byte (i.e. 4 characters long = 5 bytes)
    CHAR(50) => will take 50 bytes worth of disk/index space (per row)
    INT => 4 bytes worth of disk/index space (per row)

    Yes, you acquire a little more overhead from having to do a table lookup against your dimension table, but at the end of the day, you are saving a lot of disk/index space using an INT. Smaller column width means that more of the index can fit on any given page. More indexes per page means quicker querying times.

    Also, string comparisons actually take a fair bit more time to actually execute than the INT comparisons (it needs to pull all of the bytes into memory and scan one-by-one).

    In short, normalize your tables it'll make your life easier, cleaner, and keep your database as small as it can be.

    ~judda
     
    Last edited: Mar 4, 2013
    Gabby likes this.
  4. Hituro

    Hituro Supporter VPL Supporter Pet Game Owner VPL Member

    Reputations:
    17
    Joined:
    Feb 1, 2011
    Messages:
    1,419
    Likes Received:
    97
    Trophy Points:
    48
    When a database is opened there is overhead that relates to the number of tables, and also the size of the tables, especially with InnoDB, where you are storing all of the tables in one file. But I think that's more to do with total DB size than number of tables. I don't think there is a big downside to lots of small tables, and I think like @judda says, it's better to have more small specialised tables than big generic ones with string keys.

    For reference Grophland currently has 95 tables
     
    Last edited by a moderator: Oct 16, 2013
  5. Mio

    Mio Moderator Moderator VPL Supporter Pet Game Owner Web Designer Junior Programmer Approved VPL Artist

    Reputations:
    10
    Joined:
    Oct 4, 2012
    Messages:
    1,433
    Likes Received:
    265
    Trophy Points:
    93
    Location:
    US
    LOL anti-join order.

    Seconded. The cleanliness has it for me.
     
    Last edited by a moderator: Mar 4, 2013
  6. Myztacia

    Myztacia Web Dev VPL Supporter Pet Game Owner VPL Member

    Reputations:
    4
    Joined:
    Feb 28, 2011
    Messages:
    504
    Likes Received:
    50
    Trophy Points:
    28
    Gender:
    Female
    Location:
    California
    I agree with @judda on this as well. However, I still say do this within reason. :]
     
    Last edited by a moderator: Oct 16, 2013
  7. Gabby

    Gabby Active Member Former VPL Staff VPL Member

    Reputations:
    205
    Joined:
    Apr 1, 2012
    Messages:
    7,784
    Likes Received:
    1,347
    Trophy Points:
    113
    Gender:
    Female
    Location:
    in a song, New York
    Oh of course, I do everything with moderation (unless you're talking about cheese... or ice cream.. aaaah XD)
     
  8. BigThinker

    BigThinker Supporter VPL Supporter

    Reputations:
    2
    Joined:
    May 9, 2011
    Messages:
    937
    Likes Received:
    43
    Trophy Points:
    13
    I have about 20 tables and each table is specific per area or feature. The only table thats big, width wise is the pet table since there's a lot of info per pet, but some of the game tables are ever growing in length but have few columns.
     
  9. Gabby

    Gabby Active Member Former VPL Staff VPL Member

    Reputations:
    205
    Joined:
    Apr 1, 2012
    Messages:
    7,784
    Likes Received:
    1,347
    Trophy Points:
    113
    Gender:
    Female
    Location:
    in a song, New York
    I would personally have two tables. One for all the species information and one for all the necessary data that needs to be saved per pet. I don't know if you combined the two but I'd be careful if you did. A simple INNER JOIN query would be able to join the two tables together if you needed any information from both. A lot of columns could impact performance, it's harder to manage, and it isn't really good normalization practice.

    @judda correct me if I'm wrong. ^.^
     
  10. Myztacia

    Myztacia Web Dev VPL Supporter Pet Game Owner VPL Member

    Reputations:
    4
    Joined:
    Feb 28, 2011
    Messages:
    504
    Likes Received:
    50
    Trophy Points:
    28
    Gender:
    Female
    Location:
    California
    @Gabby That's me with Reese's, ice cream, and avocados! :]
    @BigThinker The number of columns isn't necessarily a problem. If you're creating a table just because you want to remove some of the columns from another, that probably isn't the way to go. (I'm not saying you are! I just felt it worth mentioning for future readers of the post.) Keeping them grouped in ways that make sense, such as grouping by area, is definitely a great start. As a programmer becomes more familiar with databases and grouping data, it will be easier to see where one can save on performance by breaking up tables.

    Along these lines, it's more important to get the correct datatype rather than worry about how many fields there are. If you have 10 very useful fields but they're all in the wrong datatype, you'll suffer performance later on. In particular, as @judda said, indexes should be in the most efficient way they can be.

    One final point I would like to make is that if it is becoming hard to understand where to find data in your database, you are probably breaking the tables down too much. It will be harder for yourself and future programmers to maintain the code.
     
    Last edited by a moderator: Oct 16, 2013
  11. BigThinker

    BigThinker Supporter VPL Supporter

    Reputations:
    2
    Joined:
    May 9, 2011
    Messages:
    937
    Likes Received:
    43
    Trophy Points:
    13
    Off the top of my head, I think there are two tables that are for pets. One for all the basic info and one for gaming info.. Hmm.. My entire site needs recoding because of me anyway but I'm in the process of organizing and such so I'll definitely keep the tables in mind.
     
  12. Chris

    Chris Pet Game Owner Pet Game Owner VPL Member

    Reputations:
    0
    Joined:
    Jan 12, 2012
    Messages:
    44
    Likes Received:
    1
    Trophy Points:
    8
    Gender:
    Male
    That's sounds reasonable (BTW, I'm from the anti-join order and while I love to denormalize I find that in the example you gave join is the lesser evil :D).
     
  13. Gabby

    Gabby Active Member Former VPL Staff VPL Member

    Reputations:
    205
    Joined:
    Apr 1, 2012
    Messages:
    7,784
    Likes Received:
    1,347
    Trophy Points:
    113
    Gender:
    Female
    Location:
    in a song, New York
    That means so much to me! I really appreciated this comment. <3 Thank you so much.
     

Share This Page