[SOLVED] wich engine is the best

[isaac_cm]

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

[fenway]

Too many to explain here... innodb in the default, myisam is faster, the rest are special-use only.

[isaac_cm]

so the best choices are innodb and myisam , if I used myisam does this will limit some of features I need

 

thanks

[hitman6003]

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.

[isaac_cm]

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

[fenway]

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.

[hitman6003]

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.

[isaac_cm]

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

[Tyche]

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

 

 

[hitman6003]

I did have that backwords...oops, stupid brain...I meant that only InnoDB supports transactions...sorry for any confusion.