which storage engine should I use- MyISAM or InnoDB?

I've read articles and postings about MyISAM vs InnoDB but I am still a
bit unsure about which storage engine to use for my new project.

I am developing a website in PHP/MySQL, which includes features such as
member login, insert/update/delete operations for members to maintain
their records, report generation based on database entries, shopping
cart but payments will be made through Paypal gateway and the search
facility.

This is a medium level website. What storage engine is best suited for
this site and why? I prefer InnoDB because of foreign key support. I'd
also like to know why MyISAM is not made to support foreign keys? If
you guys recommend MyISAM for me, how should I implement foreign key
support? Do I have to explicitely make provisions for foreign key
support in MyISAM using PHP?

Thanks

Re: which storage engine should I use- MyISAM or InnoDB?

crescent_au@yahoo.com wrote:
> I've read articles and postings about MyISAM vs InnoDB but I am still a
> bit unsure about which storage engine to use for my new project.
>
> I am developing a website in PHP/MySQL, which includes features such as
> member login, insert/update/delete operations for members to maintain
> their records, report generation based on database entries, shopping
> cart but payments will be made through Paypal gateway and the search
> facility.
>
> This is a medium level website. What storage engine is best suited for
> this site and why? I prefer InnoDB because of foreign key support. I'd
> also like to know why MyISAM is not made to support foreign keys? If
> you guys recommend MyISAM for me, how should I implement foreign key
> support? Do I have to explicitely make provisions for foreign key
> support in MyISAM using PHP?
>
> Thanks
>

you need to look at what makes each engine unique:

myisam -- fast for reading and inserting, table level lock which makes
it not suitable for transactions
innodb -- fast for updating and most suitable for transactions because
it uses row level locks

you certainly can use a combination of these 2 types of engines in your
database.

with innodb, you'll have cascading deletes/updates; however with myisam,
you'll have to do it yourself in the code or in stored procedures. so
maybe some of your tables should be innodb while others should be myisam.

hope this helps.

Re: which storage engine should I use- MyISAM or InnoDB?

I would recommend InnoDB because it is ACID compliant , thus very
needed for your site (you want to implement all-or-nothing bank-style
behaviour right?).

However, I noticed that InnoDB tables get a bit slower than MyISAM
tables during bulk-inserts ,especially of TEXT/Blobs and Varchars. It
is all relative. It depends what fields you want to store, how many
writes / reads and so on.

However, InnoDB is not the universal solution to any solution. You will
probably end-up using both types, especially if you write some log
information, which is not of critical importance.
Hope it helps.




Dragomir Stanchev
http://www.linkedin.com/in/dragomirstanchev
http://www.student.informatik.tu-darmstadt.de/~stanchev/CV%2 0deutsch.pdf