> I am about to start the planning process in moving my FileMaker/Lasso/PHP
> site over to MySQL/PHP.
> Before I get too far down the road, should I be using InnoDB or MyISAM
> tables? The service is initially going to be hosted on WinNT but may move
> over to Linux later.
=main response, below.
=know that you can move MySQL files between OpSys at the file level - not
sure about InnoDB...
> My main database has about 21000 records (but 400+ fields). The number of
> fields will decrease to less than 100 in the normalisation process with
> FileMaker's internal calculations/scrips being handled by eithe MySQL
> functions or PHP routines.
=probably more to the point are metrics of the volume of usage, and the
ratio of data update vs simple retrieval. The complexity of the
interrelationships between data tables for update/deletion may also come
> I built prototype about 9 months ago using MyISAM and although it was OK
> am wondering if there are any benefits/drawbacks of using InnoDB instead.
> The prototype is pretty much out of date now as the FileMker dn has moved
> in leaps and bounds since then. On top of that the requirement is now to
> interact more with the company's other databases (MySQL and Oracle).
=a FileMker "dn". Someone I should meet?
=what do you mean by "interact...MySQL and Oracle"?
> Any comments would be welcome.
=the classic advantage of MySQL is that it is lean for speed, and nothing is
going to touch it. If you are retrieving information to drive a dynamic web
site, there's nothing much else in the ball-game.
=however one of the reasons for this speed, is the removal of the need to
keep checking for various 'ifs' and 'buts' within the relational model. This
brings a set of disadvantages. Each individual database interaction, eg
INSERT data into tbl, is performed in isolation. So if you have a succession
of operations that must be performed together, or upon the 'discovery' of
any problem, all removed/not executed, ie a "transaction", then MySQL has no
programmer tool/facility to help you out. If the 'bankers problem' is a
potential issue within your system then InnoDB's locking can save your life.
Per the comment about 'metrics' (above), sometimes these matter and
sometimes they don't...
=onwards: the manual summarises InnoDB's talents thus:
7.5.1 InnoDB Tables Overview
InnoDB provides MySQL with a transaction-safe (ACID compliant) table handler
with commit, rollback, and crash recovery capabilities. InnoDB does locking
on row level and also provides an Oracle-style consistent non-locking read
in SELECTs. These features increase multiuser concurrency and performance.
There is no need for lock escalation in InnoDB, because row level locks in
InnoDB fit in very small space. InnoDB tables support FOREIGN KEY
constraints as the first table type in MySQL.
InnoDB has been designed for maximum performance when processing large data
volumes. Its CPU efficiency is probably not matched by any other disk-based
relational database engine.