SQL query audit + add database indexes

[tobyzerner]

Lots of low-hanging fruit to improve performance and efficiency. Basically we just need to set up a query log, do every action possible on the forum, and then inspect each query individually and optimize where it's not too hard.

Also add SQL indexes (we don't have any currently apart from primary keys).

There are some more interesting cases too, like:

• Flarum\Forum\Actions\IndexAction line 58: we send an API request to get the current user, but we already have the user data; we just want it formatted in an API response. We could technically call a serializer directly, but then we don't quite get all of the response features (like sideloaded data), and also Forum would depend on Api. I don't think we can optimize this case.
• Flarum\Core\Repositories\EloquentPostRepository (see big comment at top of file)

[tobyzerner]

No SQL query optimization has been done on Flarum yet, so there is probably quite a bit of room for performance gains here.

What needs to be done:

• Create a "Debug" extension which attaches the page load time and a list of queries that were run to each API response (under the "meta" key).
• Generate a large dataset (hundreds of thousands of discussions/posts) to test with.
• Analyse the queries that are run on requests to each API endpoint and work out ways to reduce/optimize them – especially making sure there are no n+1 situations.
• Add database indexes where appropriate.

[michaelmior]

I'm going to suggest https://github.com/maximebf/php-debugbar for this purpose as it's easy to use and can quickly collect a lot of helpful data.

[tobyzerner]

Thanks, will check that out.

[younes0]

if it can help: Laravel's package of maximebf/debugbar: https://github.com/barryvdh/laravel-debugbar