KI114 Posted May 28, 2009 Share Posted May 28, 2009 I've been looking into the different database engines but am still not sure which one is best to use for my tables. I'm using MySQL 5.1. TableTasks MessagesBeing created and read a lot (every page load) and needs to handle a lot of rows NewsRead a lot but rarely added. PlanetsHardly ever edited but read a lot Player LevelsRead a lot (every page load), not changed much ShipsRead a lot (every page load), not changed much UsersRead a lot (every page load, sometimes more), changed continuously I hope somebody can help me Quote Link to comment Share on other sites More sharing options...
JonnoTheDev Posted May 28, 2009 Share Posted May 28, 2009 InnoDB just because MYISAM tables crash and have to be repaired (0 recovery). This will probably start a huge thread now! Quote Link to comment Share on other sites More sharing options...
corbin Posted May 28, 2009 Share Posted May 28, 2009 InnoDB just because MYISAM tables crash and have to be repaired (0 recovery). This will probably start a huge thread now! >.< I don't think I'm even going to respond to that.... x.x Anyway, I would suggest the following (in the same order as the original post): InnoDB MyISAM MyISAM MyISAM MyISAM InnoDB Each database type has it's own advantages and disadvantages, but I'm looking at mainly two things here. SELECTing from a MyISAM table is faster, but UPDATE'ing or INSERTing into a MyISAM table can be slower. With InnoDB, the locking is per row. In other words, only the rows that will be affected are locked and all other rows can still be locked by other queries, read, or so on. In MyISAM though, the entire table is locked. So, if you have 10 UPDATE queries working on different rows, under InnoDB they could all potentially happen at the same time. Under MyISAM though, each query would have to wait for a full table lock, in which case each query would happen 1 at a time. There are other differences too that could affect your decision, but that's the main difference in this situation in my opinion. (Other sometimes important factors: InnoDB has transaction support. MyISAM has fulltext searching support. ) Quote Link to comment Share on other sites More sharing options...
JonnoTheDev Posted May 28, 2009 Share Posted May 28, 2009 LOL Quote Link to comment Share on other sites More sharing options...
KI114 Posted May 28, 2009 Author Share Posted May 28, 2009 Well I see why you chose them engines and their benefits, so thanks! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.