i'm Developing a large Website, and adding many functionalities on it. I want to know if there's any problem with making all the tables in the database InnoDB type, cuz there are so many foreign key connection within all the tables I have like 20 tables in the database, and all the tables in the database are all connected together somehow, is there any problem with this, or it's okay, thanks.
4 Answers
InnoDB is preferred over MyISAM and if you have foreign key constraints then you should use InnoDB anyway since MyISAM doesn't support foreign keys.
Also, if you want to use InnoDB, its usually recommended that all tables use InnoDB. It provides a number of advantages anyway so I'd say yes, make all tables InnoDB unless you have a good reason not to (i.e. you need MyISAM Geospatial extensions or Fulltext and your InnoDB version doesn't support those features).
2 Comments
Samo Adx
okay thanks man, but wats MyISAM Geospatial extension or Fulltext, what does it mean, and how does it relate to database transactions.
drew010
It doesn't relate to transactions, but both are features available for some time in the MyISAM engine, but not available to InnoDB. Fulltext is now supported by InnoDB in MySQL 5.6, but this version is not yet widely used. Fulltext is used for indexing large blocks of text for text searches. You probably don't need them since you aren't familiar with them. See spatial extension and fulltext on the manual if you are interested to know about them.
Nothing wrong with it at all, you should use whatever functionality is available to you such as the INNODB engine.
Comments
Problems, no. But what dbm are you using? (InnoDB is the default storage engine for MySQL) So there should be no problems, assuming you are using MySQL or any others that support InnoDB. but what are you doing with the tables and what would cause a problems, or that you think would cause problems ...
4 Comments
Samo Adx
nothing i suppose, i just feel maybe there's a problem with that, but since u said its okay to make all tables innodb, i'll go on with that. And by the way, i'll be making real time transactions on the table and making concurrent connections and changes, so i was wondering if making it innodb still supports this functionality.
jackeduphard
I am building a task management system right now, and I have uID's on all the tables and they are all InnoDB based. So imo you should be fine, just make sure you know what you are needing to get back and put back into the DB and it sounds like everything you need is going to be fairly strait forward. like @drew010 said, if you dont use the indexing of the text or dont need to then dont worry about it. (U sound like me :D )
Samo Adx
okay i understand, but if i don't use indexing of the text, how do i do searches on the database, like if a user wants to search, won't that cause any drawback with innodb?
jackeduphard
look back at @drew010 last comment and read through the links he sent you. They should clarify what you are worrying about, you will still be able to, he saying that if you don't even use that type you are going to be ok. You will still be able to index and search that will not change. For you, InnoDB is going to be fine. (don't overthink it)
As far as I am aware the only non-trivial reason to choose MyISAM over InnoDB is for Full-Text indexing. Apart from that, InnoDB's support for transactions and foreign key constraints make it a much better choice for the vast majority of applications.
That said, there's nothing stopping you from using a few MyISAM tables alongside InnoDB tables if you require some functionality from MyISAM.
1 Comment
Samo Adx
i understand, but all the tables in the database are all so inetrconnected together that i have to link them togetehr using foreign keys, hence the reason i'm using innodb