Speed is so arbitrary. As you may know it depends on soooo many factors including but not limited to MySQL server machine speed/performance, web server machine speed/performance/memory, how big a table and database is, how many keys/indexes used/created, how the query is written, etc.
I would imagine that the use of foreign keys decreases speed. INNODB supports Foreign Keys while MyISAM doesn't. But here's the main difference I see of using InnoDB types:
Lets say that you need to do various updates and inserts across different tables. When you have proper error checking in place (like stopping when an error occurs), and an error does occur it will leave things out of sync a bit. I mean some things might get inserted/updated while others don’t. This may or may not be a problem for you.
If you require that everything gets inserted/updated all at once correctly, then you need to use transaction based MySQL tables (InnoDB types). That way before you start any inserts/updates you would issue a BEGIN and after everything is successfully processed, you would issue a COMMIT. If there’s an error, you would issue a ROLLBACK and all the rows that were inserted/updated will be backed out (undone/deleted).
The best example I can give you about this, is in a banking scenario. For example, if money is transferred from one person’s account to another, there will typically be at least two queries. One transaction is to transfer money from person A’s account, and another to deposit the money into person B’s account. What if something goes wrong, and the system crashes or gets an SQL error after the first query is completed, but before the second one is complete? Person A will have their money removed from their account, and believes the payment went through. But person B will believe the payment was never made (never got the money). In this sort of case, it’s important that either both queries are processed together, or neither not at all. To do this, you wrap the queries together in what is called a transaction, with a BEGIN statement to indicate the start of the transaction, and a COMMIT statement to indicate the end. Only when the COMMIT is processed, will all the queries be made permanent. If something goes wrong in between, you can use the ROLLBACK command to reverse the incomplete part(s) of the transaction.
There's other considerations other than just speed. Like backup!
With InnoDB tables, you need to either take the database server down or shut out access from clients. There is an expensive tool that allows online backups of InnoDB tables, called InnoDB HotBackup: http://www.innodb.com/order.php
One of the easiest ways to back up tables is with the BACKUP command. As far as I know, it only works with MyISAM tables. BACKUP places a temporary read lock on a table before backing it up to ensure that the backed-up table is consistent.
hth.
