InnoDB, Foreign Keys and Not Null
I have been surfing around a bit for db optimization and came across three thesis that I wanted to discuss here. I'm no expert and I wouldn't bet on any of this points but I found them convincing and worth considering. So what follows below is no knowledge or truth but simply thoughts I'd like to share and discuss.
Maybe I start with the least effective one:
Saving memory space by disallowing Null values in columns
Thesis: If you don't allow Null values in columns, you will need less memory
Explanation: if you choose for example integer for your column and allow Null values, you will have to store either integer or Null and so you will have to consume more memory per column. Easy: integer is one piece of info, integer or Null are two different kind of data.
My opinion: This seems to be design decision that could give only a small benefit. Nevertheless it would be better than nothing. I don't know if Vanilla needs Null or if it couldn't be substituted by 0 or '' where it is used by now. "null" is found in about 250 files in my local Vanilla 2.1b2 copy (might also include plugins). So this can not be easily judged.
Beside from small memory benefits when Null is abandoned, I simply don't like Null because Null means dontknowanddontcare in most cases.
10 years ago I was working with databases quite more often than nowadays. I remember than Null values where in 90% the reason when something which seemed simple did not work.
So I would like to abandon Null and if it has a performance (or better resource) advantage too, then I would think it would be worth the effort to go through Vanillas sources.
Foreign Keys to speed up queries
Thesis: Foreign Keys speed up joins
Explanation: I don't remember well, but it was something like helping the query compiler to optimize queries? I almost forgot it, sorry...
My opinion: Whether you look at discussions or user info, Vanilla uses a lot of joins, so if there is something that's speeding up joins, it fit's well to Vanilla.
Downside of Foreign Keys seem to be that writing actions will take longer. So there seems to be a general decision again: is speeding up read actions worth slowing down right actions. I'd say yes, for a forum! You will have a huge amount more reads than writes to your db. So if you sacrifice writing speed but gain reading speed for that, go for it!
InnoDB is better than MyIsam
Thesis: InnoDB is the better choice for a forum.
Explanation: MyIsam (M.) is blocking the complete table whenever any query uses the table, InnoDB (I.) locks only the current row. I. allow to use foreign keys, which should be a benefit (see above) and M. does not, I. is actively developed and might become even better in the future while M. is old fashioned and kind of fix.
My opinion: I was shocked when I read that M. is blocking the complete table on every query. That is simply not acceptable to me.
The downside of I. is, that it does not support fulltext indexing of columns in "older" versions of MySQL. I think it has only been implemented in the most current version (5.6). Vanilla relies on that fulltext index for its search capability
But what if someone implements a basic search plugin that uses an internal index table instead of the built in search function? Parsing each post on save, store the words of the post in an index table and use that one for a custom search function should be possible. If such a plugin would exist, comment and discussion table could be used with the InnoDB engine.
Well, what do you think of this three tweaking possibilities? Are they worth any effort at all?
I wanted to test the InnoDB and Foreign Key effect for discussion and comment table but to my shame, Ihave to confess that I didn't even know how to do this. If anybody can point me in the right direction, I'd be happy to provide some numbers.