New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
SQL query audit + add database indexes #127
Comments
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:
|
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. |
Thanks, will check that out. |
if it can help: Laravel's package of maximebf/debugbar: https://github.com/barryvdh/laravel-debugbar |
Hello! You can use Mysql profiling builted functions, for example. |
I would like to suggest another course of action. Analysing queries is fun stuff, but this mostly results in theoretical optimisation strategies. If you really want to improve performance it makes more sense to test with a database with thousands of users and millions of posts. This will make it much (much) easier to spot bottlenecks and you'll spot actual problems instead of indices which are missing only in theory. Creating this database can be done with a fixtures script or by converting an existing large forum. Advantage of fixtures is that this is trivial to share with other developers and it's easier to adapt to the specs of your development environment. The main advantage of converting an existing forum is that this results in a very realistic database. |
@BartVB Good thinking - we can integrate this when we finally get to building the migration tool. Still, there are some places where indices are clearly needed for basic filtering tasks, if I'm not mistaken... |
|
@luceos List of database indexes to be added (probably in a separate PR from #1344, that one's already big enough) (sorry, have used the old column names): coreusers
discussions
access_tokens
auth_tokens
email_tokens
notifications
password_tokens
posts
flarum-ext-lock
flarum-ext-sticky
flarum-ext-flags
|
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:
The text was updated successfully, but these errors were encountered: