I'm busy designing a database which is going to be running on MySQL. I have done a lot of researching on various elements of the database (a lot of which has been on these forums) but am having difficulty in working out some specifics about the two table types of MyISAM and InnoDB.

I realise that MyISAM doesn't support refferential integrity, but when I have tried producing tables in InnoDB the size of the tables is HUGE in comparrison to the same table in MyIsam.

So I guess questions are:
1) Does an InnoDB table always take considerably more disc space to store than a MyISAM table (With the same data)?
2) Is there a difference in terms of access speeds other than the size of the table (important as the MySQL server is running on ancient hardware).
3) Anything else that I need to consider when choosing the type (read about InnoDB becomming proprietry, so future support?)

Thanks in advance for any replies and information on this topic

P.S. Sorry if this has been answered before but couldn't find anything while searching the forums (It may just be that I am a bit crap at searching).

Personally I'd just stick with the default engine (MyISAM) and only swap to InnoDB if either transactions or foreign keys are essential to your design. MyISAM will always produce more compact data which will be better for most systems while the InnoDB data tends to be more efficient on huge tables.

Mike

If you intend to code in assembler for a 24 by 80 character screen, then I can make an argument for using MyISAM. When you are willing to sacrifice everything (including safety) for speed and you need to keep hardware requirements very small (such as for handheld computers running strictly on NV-RAM) then MyISAM is the tool of choice. NB, this is a very rare occurance these days.

If you need basic data integrity features like transactions and referential integrity, then I see Inno-DB as the only option. While Inno-DB definitely does add some overhead, I see that as a small price to pay for data integrity.

-PatP

I don't think it's that clear cut ...

Performance: depends how your database will be accessed - If your system is mostly reads with batches feeding data in then MyISAM will be faster however if there are lots of concurrent updates/inserts etc then you'll definitely want the row locking available in INNODB.

Transactions: I guess it depends on what type of system you're producing - if it's financial then I'd definitely go with INNODB.

Full text searching: if you want to do this then you'll have to use MyISAM.

Storage space: MyISAM will always use less space than INNODB.

Data security: With MyISAM you need to periodically run a check table on each table to ensure things stay stable. This isn't necessary with INNODB.

You can mix and match by having different engines for different tables but remember that rollbacks won't apply to the MyISAM tables. It is also possible to get the features of both sides for one table by using replication but this is probably needlessly complex. At the end of the day MyISAM is the default setting for MySQL and so I tend to use MyISAM. If I need transactions or FK then I switch to INNODB.

Mike

Thanks for the informative replies guys. Sorry not been back in a while to say thanks, been busy with some other stuff, the joys of trying to learn how to create web pages these days, so many new technologies since I last did it!!

Thanks again anyway

In the data of the modern hard drive Storage space should never be an issue for database systems now. My personal preference these days is InnoDB so that I may enforce referential integrity as much as possible. Transactions and rollbacks come in handy when you're doing cross table inserts that are reliant on one another, which is something that an application would need to handle if you're using MyISAM. Rolling back in an application = nightmare...
Just my thoughts and preferences

For the record I've changed my mind on this subject and would probably go with INNODB as well now.

On the one hand it's good to have more complete answers to questions, on the other hand this thread is absolutely ancient - 4 years in web terms is almost prehistory.