MyISAM to InnoDB

[brunoais]

@VSE, When the table is created, you can mention which storage engine you want to use, if it doesn't exist, it uses the default one instead.

If a storage engine is specified that is not available, MySQL uses the default engine instead.

BTW,

For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications.

[Marshalrusty]

phpBB can force the database engine, but it's not its place to do so. Either the administrator knows what they are doing and has intentionally specified the correct engine to use, or they have no idea what they're doing, in which case the logical thing to do is trust the server administrator, who hopefully had set the correct engine for the setup. Either way, it seems to me, we should not be forcing this one way or the other.

Naturally, it is not possible to swap from one engine to the other on the fly (such as during an update) as doing so requires an entire rebuild of the data storage containers, which can take minutes (on a small board) to many hours (or a large one).

[brunoais]

Is it OK, then, to add forign key definitions support to DBAL? In MySQL's case, if the selected default DB engine is InnoDB, then phpBB could have optimized DB speeds for the multiple JOIN's it has.
Currently, phpBB doesn't use that so JOIN's are way slower than how they could be.
We can check on foreign key support on case-by-case basis for other DBMS.

[DavidIQ]

You'll need to provide some numbers in order to make a decision like that as adding foreign keys will break many things. Even if we decided on doing that I don't think it would/should be done in 3.2.

[Alien_Time]

In MySQL's case, if the selected default DB engine is InnoDB, then phpBB could have optimized DB speeds for the multiple JOIN's it has.
Currently, phpBB doesn't use that so JOIN's are way slower than how they could be.
We can check on foreign key support on case-by-case basis for other DBMS.

That is exactly right... There is a reason Innodb (or other similar SE) came into existence and currently used as the default engine because of the limitation & performance on MyIsam. I know now might not be the time for phpbb to specifically use Innodb, but this is something that needs to be adapted in the future. Probably by the time 3.2 comes out, it should not be an issue for phpbb to use Innodb for the latest version since most users should be having the later version of Mysql (or would need to upgrade to a later version) to benefit from additional benefits it has to offer.

[brunoais]

You'll need to provide some numbers in order to make a decision like that as adding foreign keys will break many things. Even if we decided on doing that I don't think it would/should be done in 3.2.

What would break by using foreign keys?

[DavidIQ]

You would get errors when inserting, updating, deleting of certain data since the foreign key would mean that the table is dependent on another table. Right now it doesn't mater so there's no problem. Once you add a foreign key constraint you can get errors such as "Violation of foreign key constraint" when performing inserts or deletes. If the logic is not taking foreign keys into account then errors are going to happen (think about the queries that get processed when splitting a topic or moving a topic or deleting a user, etc.). Even though possible errors on insert wouldn't be avoidable the delete errors could be avoided with cascading deletes but not sure how that works in MySQL.

Given that we have a number of indexes I'm not sure how much of an improvement in performance there would be, if any, by adding foreign key constraints, which is why I said that you should probably produce some statistics showing how much of a difference there is between having foreign keys and not.

[brunoais]

We just use the "ON DELETE SET NULL ON UPDATE CASCADE" and that shouldn't bother. Anyway, we have testes for all of those, right? Ultimately, it will only actually improve performance and errors are, most probably too rare.

If cascading deletes is used, it is quite direct, actually:
Considering three rows A, B, C. They are all in different tables. They are related as A has foreign key referencing B, B has foreign key referencing C.
If C is deleted, C, B and A are deleted. If B is deleted, B and A is deleted, if A is deleted A is deleted.
Foreign keys are 1 to many.

Do you have any good large example large database data I can use for testing? It would be useful to build some benchmarks on that instead of inserting random data directly into my personal DB. Benchmarks are only useful with really crowded DB's, not very small ones like the one I have for testing.

[Marshalrusty]

Oh, errors won't be rare at all. When I ran a few scripts on the phpBB.com a few years ago, I found lots of stranded posts (not attached to any topic) and topics (not attached to any forum). Then there were a few users without a group. Most of these were very old and carried over from phpBB 1 or early phpBB 2 days, but there were more recent ones as well. All of these inconsistencies don't currently cause any problems, but adding foreign keys would have generated errors. So it's not a small task to implement, and would be a massive undertaking for support, when they start causing problems for people who have no idea what they are.

Which brings me to my main point: where did you get the idea that adding foreign keys improves performance? It definitely slows down writes, and I cannot find any information about optimizations (and their level of effect) for reads. So let's go back to the part where you justify why this is even a good idea to consider.

[JoshyPHP]

We just use the "ON DELETE SET NULL ON UPDATE CASCADE" and that shouldn't bother.

Can you even do that if the column is set as NOT NULL, which most of them are?

Which brings me to my main point: where did you get the idea that adding foreign keys improves performance?

I've seen this myth being perpetuated online for as long as I can remember. In my experience, people can rarely trace back how that idea came to them. My hypothesis is that some people found better performance after adding foreign keys in databases that had no index. Foreign keys usually create or require a matching index, therefore adding foreign keys to a table that had no secondary index would sometimes have the same benefits than adding the right indexes, or close to.