Hi George, > 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 into play. > I built prototype about 9 months ago using MyISAM and although it was OK I > 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 on > 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. =Regards, =dn --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
