Jump to content
  • Advertisement
Sign in to follow this  
joechimo

[MySQL] MyISAM vs InnoDB

This topic is 3477 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I've recently began the design of a web-based MMO. After some discussion, we have chosen to go with MySQL 5. Being an indie developed game we are not anticipating a tremendously large number of users, at least not all online at once. Still, I want to make sure that I eliminate any unneeded weight during the design of the database. I am curious if anyone would be able to toss me some useful knowledge concerning the use of MyISAM over InnoDB. The fact that the two will work together, I am under the impression that it would wise to set reference tables, ones that contain base data that is only being referred to, to MyISAM. An example of what I mean, if you don't fully understand what it is I am asking: A table named 'inst_item' is populated by all of the data pertaining to each item object instanced in the world. Table 'inst_item' does not contain base scores for the items, only modified ones, and refers to table 'ref_item' which contains all the base data of the items. Table 'inst_item' uses the InnoDB engine, the rows are constantly being updated, but since table 'ref_item' is only being referenced and never changed, would it be beneficial to use MyISAM for it? This is the largest relational database I have ever worked with, so I am still researching the best methods to optimize performance. I would love any advice. Thanks in advance, Joe

Share this post


Link to post
Share on other sites
Advertisement
One way to optimize performance is just use MyISAM across the board. But then you'll lose referential integrity. I don't know if referential integrity works when the reference table is MyISAM. My personal preference would be to use InnoDB and optimize around that solution (eg. use a big cache). If you have a referential integrity violation it means that something is wrong in your design and you want to know about it before it snowballs.

Since you were asking for some facts, one thing you should know is that referential integrity checks affact only write operations, so for reading, InnoDB and MyISAM would have about the same performance.

Share this post


Link to post
Share on other sites
I use InnoDB for everything. However, if you have tables that are truly just read-only, then you can use MyISAM for those, as long as there are no foreign keys that refer to those tables from elsewhere.

Note that if you have truly read-only tables, you can probably just load the data of those tables into RAM on start-up, and use an in-RAM hash table or map to find the data, rather than hitting the database all the time, for a quite noticeable speed-up. I even know of systems that use the DB for design, but build an in-memory data file out of the database for read-only data, and load the data file (rather than the database) on start-up. Doing that also allows you to ship that data file to clients, which might be useful for things like a table of all trees and boulders (and other decoration objects) in a level.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!