[RFC] Add foreign keys to phpBB3.1 to make it faster

[brunoais]

One of the bottlenecks of DB speeds with phpBB is the lack of indexes.
The main indexes that are missing, are the foreign keys.
As confirmed by hardolaf phpBB does not use foreign keys and that's a place where would could and should invest some time to speed up phpBB's processing.

For example, what is being asked in that same idea topic could be made less painful to phpBB's execution by adding the appropriate foreign keys.

We could also use this RFC as an excuse to review all indexes and update the indexes that phpBB uses to make sure that they are being used correctly and none is there just to waste time while doing updates to the respective table.

[AgustinL]

+1 to this.

[DavidIQ]

+1 but I'm not sure 3.1 should be the target. This is much bigger/complex than just defining a few foreign keys.

[brunoais]

I truly believe that 3.1 can be the target as long as it's just the DB indexes.
We can then make optimizations inside this in 3.2.

[code reader]

+1 but I'm not sure 3.1 should be the target. This is much bigger/complex than just defining a few foreign keys.

??????
as far as i understand, this RFC is about one thing, and one thing only: adding some foreign key indexes. how is it "much bigger/complex than just defining a few foreign keys"? what else is there in this RFC?

of course, we can open another discussion about db optimization in general, but such a discussion will not be an RFC - more like "brainstorming". if this is what David meant, then i agree - elaborate db optimization should probably not be for 3.1.
this RFC is (if i understood what i read), just about adding some indexes.

of course, there is a good question for the OP: can you be more specific and give some details, and list which foreign keys should be added in your opinion?
even better, can you post some measurements that will substantiate the claim that those changes will actually improve performance?

peace.

[DavidIQ]

Foreign keys means tables depend on each other and inserting or deleting data becomes a hassle. I'm not sure we want additional indexes. I think these foreign keys would simply replace some of the existing ones.

[JoshyPHP]

As far as I know, foreign keys only slow down queries in MySQL. Write queries, specifically.

Foreign keys are only constraints put on top of indices. They don't improve read queries and they have a cost on write queries.

[bantu]

Please explain how foreign keys achieve speedup.

[brunoais]

Foreign keys work as indexes the same way as the INDEX keys.
They give a performance impact of the same size as the other indexes on operations as INSERT, DELETE, etc... that changes the number of rows in a table or changes the indexed content (if the indexed content is not changed, there's no performance impact, ofc )

On the other hand, they are able to give you a "bridge" between tables that is very useful (performance-wise) when using any kind of JOIN of tables in a SELECT statement.
Recently, I got two examples (one very small and one quite significant) of performance improvement. It didn't happen to me but they can be used as a small support to what I'm mentioning.
This is the example: original url (google translated)
It was a DB query (check the original, google's translation is cutting the SQL) that had two sub-queries and those sub-queries had joins. At that moment, the DB was still quite small so It was only taking 2 seconds to execute (last paragraph in the 1st post). After adding the indexes the way I suggested (changing the tables to InnoDB didn't help the speed, only the indexes) he was getting responses instantaneously (so I guess it was <0,2 seconds).

Another example:
This time it's a closed source project in a freelance very small work I did, so I'm unable to bring you practical examples, I may only talk about it.
There was a query that wasn't made very often but it was a hassle to execute it because it was taking a whooping ~15 seconds to execute.
I had found two issues with that query. First, it was doing 3 table joins second, it had a couple of "OR".
To start off, I got rid of the "OR" by using another JOIN. Instead of slowing the query down, it actually had speed up to about 13s. Then I changed some indexes in the tables themselves to accommodate these changes I had made (without "killing" the other queries). The speed improved to ~7s. Then... I added the foreign keys in a well planned way (I hope). That SELECT's speed was reduced to ~0,7s (sometimes ~1s)!!!
4 table joins (3 tables total), with a nice amount of data where, just before the proper indexes was ~7s became ~0,7s. I don't remember the exact values as this was a while before that forum post (in early 2012, I think) but I think the idea is here.
The INSERT (for just 1 row) with the largest performance impact became ~0.5s slower than it was before the foreign keys... It's the price to pay for such improvement in the SELECT statement.

Do these examples work for you or do I need to search for real technical information to explain it to you ? The issue is that I never studied hard on how it improves I just know the basics (the bridge connection analogy).

[EXreaction]

A better example could be shown if you benchmark some of the slower queries we run and then change the tables to use foreign keys and benchmark it again.

We don't have any queries similar to what you have described so far; I'm not sure there is a single query anywhere in phpBB besides admin tools that take even 1s.