Hex's noobie Database questions

[Hexgenesis]

Well I'm biting the bullet and really digging into this stuff, after years of thinking it was too hard, I think it's coming together. Fireboar's "nothing to 100% tutorial" helped a lot, thanks. (In fact I'm looking for more tutorials like that...)

First question I have is I see some systems use 'ENGINE=INNODB' at the end of a SQL statement, and some say 'ENGINE=MYISAM' at the end. Further I've seen some code say 'TYPE=MYISAM" instead of "ENGINE="

I understand INNODB and MYISAM are two different 'engines' (based on the word) that seem to do the same thing (i.e. run the SQL). My question is - what's the difference? Do I want to mix and match them, or make sure all my SQL statements use the same one? If so, which one?

I'm using the newest version of MySQL (5.5), I"m on a Windows XP setup. That may change when I'm all done but for developing this is just the most practical. Learning Linux on to of all this other stuff proved top be too much. Baby steps

[Baaleos]

Apparently INNODB is more resillient than the MYISAM engine.
eg - Recovery from crashes etc, and different method of saving the data in the database.
MyISAM apparently uses 3 files for a database etc.

http://en.wikipedia.org/wiki/InnoDB


Innodb is the default engine used with mySQL post 5.4 or something like that.

[Hexgenesis]

Thanks, OK, so I should just INNODB for everything then it sounds like.

[Fireboar]

Yeah, generally using InnoDB for everything is a good idea. It's like the XHTML Strict of MySQL, and forces data to be consistent at all times - if a query that would result in an inconsistency is run, an error is thrown, which is a Good thing: errors mean problems in your code, and you want them to be noticeable so you can fix the problem, rather than simply ignored.

MyISAM doesn't support foreign key constraints, so while you can still run queries that join tables together, the join has no meaning internally, so you could reference a field in a different table that doesn't exist.

As an example of this, try following my tutorial, but using MyISAM instead of InnoDB. When you get to the DELETE part, the posts table won't update itself automatically - it will continue to reference user ID 1 even though no such user exists. The result is a bug - if a user deletes their account then all their posts will be missing a username and signature, and the system won't automatically fall back to "anonymous" (or whatever) unless you explicitly code something to do so in, because the user ID isn't null.