Using InnoDB for almost all my tables, what's your idea?!

[ConfusedOfLife]

Hi

This is the first time that I wana write a project with InnoDB table type. These tables are related to each other and I wana have all CONSTRAINTS checked by MySQL itself, not that I have to write all those stupid DELETEs and UPDATEs myself! And because InnoDB is the only table type that supports FOREIGN KEY constraints, I wana use it for my project. But before using it, I wanted to know your ideas, whether it's good to have InnoDB instead of MyISAM. As I said this is the first real project I'm doing with InnoDB and I don't wana end up with some slow tables in the next few month that I have to re-write everything from the scratch! There are going to be lots of records and I wana know if InnoDB can take it as good as MyISAM.

BTW I'm using 4n relationships, like

table 1 -> table 2 -> table 3 -> table 4

read -> as "is a child of"

Thank You

[Kiwi]

InnoDB is slower than myISAM - but unless you're running large tables (by large, I mean tables with upwards of 100k records). And if you're operating with that much data, having the robustness that InnoDB offers in terms of enforcing database integrity is usually worth it. When you're running with tables that big, a good design (including good use of indexing) is going to give you a lot more speed than a hyperactive select statement.

I am fairly biased: I don't consider mySql/myISAM to be a relational database - largely because it isn't. Any moderately sophisticated application requires a relational database: so it really is a no-brainer.

I would suggest making sure you're running at least 4.1.3.

[ConfusedOfLife]

Thanks for your answer. I really think the same, using MyISAM without any constraints is not really what I want! I tried it b4 in my previous apps and it's a pain in the a**!

So, your'e saying you don't consider MySQL as a RDBMS, so, wuddya use? Just for curosity! Do you always use InnoDB or another RDBMS?

BTW this InnoDB thing doesn't have triggers either, am I right? Not having triggers and check constraints is another problem that makes me hard code it in my app :(

[Kiwi]

Triggers are due out in version 5.1 or 5.2 (I can't remember which off the top of my head). Now that mySql has then, transactions and foreign keys, it is a RDBMS system. But with the default MyISAM tables, there are no relationships maintained at the database level.

I do most of my work on Oracle. I don't do a lot of on-line web-development at the moment.

<edit>
Views are also due out about the same time. Views are something I really miss in mySql. They're due in version 5.0, with stored procedures also in 5.0 and triggers in 5.1</edit>