Welcome to Virtual Pet List - the fastest growing online games forum on the internet

Would you like to become a member of the largest and most successful virtual pets & sim games community on the internet today? We've been opened since 2011 and since then, we've been providing developers, artists, players and writers with the most relevant, up to date, quality and in depth content covering the entire online games community. So, if you just like virtual pet sites, we have you covered. But, if you prefer sim games, well we're here for you as well. However, if you're a new game developer and you're looking to show off your game to all of our members, then we'd love to hear all about your game in our sneak peeks forum. Just because our name is Virtual pet list doesn't mean pet games is the only thing we talk about. Our community talks about technology, making money, making art, writing and a lot more. So, please don't be afraid to say hello to everyone here because you never know what type of friends you'll make on our community. We strive to be friendly and offer positive discussions. We're very passionate, caring and hard working members of these niche, so if you hear about a new pet game from your friend, then it's highly likely that your friend found that game on our forum. If you want to learn anything about developing your own online game, then just go through our guides forum. You'll notice that whatever you're seeking on other developer's communities has more than likely already been talked about, researched and has already been put into good use by highly skilled developers.

  1. An upcoming virtual pet site that's owned by one of our moderators, Pepper-headAn upcoming virtual pet site that's owned by one of our moderators, Pepper-head sim game where you can breed your very own cats Kaylune, a virtual pet site Grophland.com : Breed virtual pets, Play, Explore
    VigLink badge

    Comment, rate or review Virtual Pet Sites, Sim Games or Role Playing games.
    Help vpl reach 10k users by Promoting us or purchase advertising
    : Development Guides - The Admin Effect - Browser games

    Donations are now being accepted again!!!
    What would you like us to improve?

Too many MySQL tables?

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

  1. 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
    Your banner/button can be located here for an entire month or year, please see our advertising on virtualpetlist thread for more information.

  2. 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. 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.

    #3 judda, Mar 4, 2013
    Last edited: Mar 4, 2013
    • Like Like x 1
  4. 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 @juddajudda says, it's better to have more small specialised tables than big generic ones with string keys.

    For reference Grophland currently has 95 tables
    #4 Hituro, Mar 4, 2013
    Last edited by a moderator: Oct 16, 2013
  5. LOL anti-join order.

    Seconded. The cleanliness has it for me.
    #5 Mio, Mar 4, 2013
    Last edited by a moderator: Mar 4, 2013
  6. I agree with @juddajudda on this as well. However, I still say do this within reason. :]
    #6 Myztacia, Mar 4, 2013
    Last edited by a moderator: Oct 16, 2013
  7. Oh of course, I do everything with moderation (unless you're talking about cheese... or ice cream.. aaaah XD)
  8. 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. 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. @GabbyGabby That's me with Reese's, ice cream, and avocados! :]
    @BigThinkerBigThinker 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 @juddajudda 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.
    #10 Myztacia, Mar 5, 2013
    Last edited by a moderator: Oct 16, 2013
  11. 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. 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. That means so much to me! I really appreciated this comment. <3 Thank you so much.

Share This Page

  • About VPL

    We are a community of artists, writers, programmers and general users who have a vested interest in virtual pet games. All of us are from different backgrounds and yet we group together with one goal, to ensure our community is one of the best!
  • Like VPL on Facebook!

  • Support VPL

    We have to face that the site doesn't run for free sadly. If it did, we would be all set but unfortunately the costs are getting higher and higher as we grow. We offer members a Supporter premium usergroup. If you donate to VPL you are joined to this group and you get many perks that members do not get.

    Donate to VPL!