isaac_cm Posted July 31, 2007 Share Posted July 31, 2007 I read some article about different mysql storage engine but I need to know which is the best practically to performance and still able to use transaction, foreign keys and locking thanks MyISAM Default engine as of MySQL 3.23 with great performance MEMORY Hash based, stored in memory, useful for temporary tables InnoDB Supports transactions, row-level locking, and foreign keys BerkeleyDB Supports transactions and page-level locking BLACKHOLE /dev/null storage engine (anything you write to it disappears) EXAMPLE Example storage engine ARCHIVE Archive storage engine CSV CSV storage engine ndbcluster Clustered, fault-tolerant, memory-based tables FEDERATED Federated MySQL storage engine MRG_MYISAM Collection of identical MyISAM tables ISAM Obsolete storage engine Quote Link to comment https://forums.phpfreaks.com/topic/62653-solved-wich-engine-is-the-best/ Share on other sites More sharing options...
fenway Posted July 31, 2007 Share Posted July 31, 2007 Too many to explain here... innodb in the default, myisam is faster, the rest are special-use only. Quote Link to comment https://forums.phpfreaks.com/topic/62653-solved-wich-engine-is-the-best/#findComment-312093 Share on other sites More sharing options...
isaac_cm Posted July 31, 2007 Author Share Posted July 31, 2007 so the best choices are innodb and myisam , if I used myisam does this will limit some of features I need thanks Quote Link to comment https://forums.phpfreaks.com/topic/62653-solved-wich-engine-is-the-best/#findComment-312180 Share on other sites More sharing options...
hitman6003 Posted August 1, 2007 Share Posted August 1, 2007 till able to use transaction, foreign keys and locking Only MyISAM supports transactions and foreign keys. Locking is a part of any and every database engine. Quote Link to comment https://forums.phpfreaks.com/topic/62653-solved-wich-engine-is-the-best/#findComment-312442 Share on other sites More sharing options...
isaac_cm Posted August 1, 2007 Author Share Posted August 1, 2007 but I read that MyISAM tables can be corrupted easily, I think I will continue to use InnoDB I think it is better but can I use different tables engines in the same database ? thanks Quote Link to comment https://forums.phpfreaks.com/topic/62653-solved-wich-engine-is-the-best/#findComment-312712 Share on other sites More sharing options...
fenway Posted August 1, 2007 Share Posted August 1, 2007 but can I use different tables engines in the same database ? Yes any table can be corrupted "easily", it's just that myisam has no journaling. Quote Link to comment https://forums.phpfreaks.com/topic/62653-solved-wich-engine-is-the-best/#findComment-313059 Share on other sites More sharing options...
hitman6003 Posted August 1, 2007 Share Posted August 1, 2007 Define "corrupted easily". When the database suddenly dies...because the process is killed or the power to the server is cut, etc...it can cause harm to any of the database files. MyISAM has a greater potential for problems because it does table level locking during inserts. That means that if an insert is occurring when the database dies, that table is left in a locked state. It is easily fixed by repairing the table. There is no one engine better than another. If you need transactions however, there is only one choice, at least until MySQL 6 comes out. If you have a read heavy environment, and don't need transactions, then **generally speaking** MyISAM is going to give you better performance. Due to it's table level locking strategy however, in a heavy insert environment, MyISAM is not always the best choice...InnoDB is the better choice of the two major engines. Quote Link to comment https://forums.phpfreaks.com/topic/62653-solved-wich-engine-is-the-best/#findComment-313326 Share on other sites More sharing options...
isaac_cm Posted August 2, 2007 Author Share Posted August 2, 2007 Thank you all guys , I appreciate your help here BTW: any one used flex before ? I created a thread about it but no replays http://www.phpfreaks.com/forums/index.php/topic,152119.msg656727.html#msg656727 thanks Quote Link to comment https://forums.phpfreaks.com/topic/62653-solved-wich-engine-is-the-best/#findComment-313888 Share on other sites More sharing options...
Tyche Posted August 2, 2007 Share Posted August 2, 2007 till able to use transaction, foreign keys and locking Only MyISAM supports transactions and foreign keys. Locking is a part of any and every database engine. This is wrong - MyISAM tables do not support Transactions - You need to use the InnoDB engine if you require Transactions, the same goes for Foreign Keys (They can be defined for MyISAM tables but there is no referential integrity support) . Quote Link to comment https://forums.phpfreaks.com/topic/62653-solved-wich-engine-is-the-best/#findComment-314129 Share on other sites More sharing options...
hitman6003 Posted August 2, 2007 Share Posted August 2, 2007 I did have that backwords...oops, stupid brain...I meant that only InnoDB supports transactions...sorry for any confusion. Quote Link to comment https://forums.phpfreaks.com/topic/62653-solved-wich-engine-is-the-best/#findComment-314310 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.