Level 39
-1 easy - create a daily job that store your result in a table and use this table for your dashboard
-2 real time - imagine a function that calculate your average from your previous one calculation
Feb 3, 2022
6
Level 5
Optimise a query working out average duration in seconds
I have this query:
$avglisteningtime = TrackTimes::query()->where('user_id', '=', $user->id)->avg('time_listened');
And this is getting the average listening time for all tracks owned by a user, of which there may be thousands of lines. The TrackTimes model looks like this:
ID: 7
CREATED: 2018-11-24 23:10:22
UPDATED: 2018-11-24 23:10:22
TRACK ID: 4
TIME LISTENED: 267 (in seconds)
TRACK TOKEN: p3AtlB92pKvvAwHSrcKdejhOFIzzt2TbSPFOTzDZa6FKwJv4Ho
USER ID: 75
For any one track there may be thousands of the above, and for any one user there may be dozens of tracks.
The app is getting quite big now with a lot of users and plays so this query is starting to slow down the dashboard page load.
I would like to either optimise the query OR is there a way to make the page load and have this data fill in after the fact, once it has been generated?
Thanks
Level 56
Also, besides @sr57 suggestions, check if the database have an index for the user_id column on the track_times table.
If there is one, and performance is still satisfactory, I would try one of @sr57 's suggestions.
As an example, Laracasts does hourly statistics updates for the Leaderboard to avoid slow calculations on that page.
Level 5
@rodrigo.pedra good shout. I haven't added an index and I'm about to ask a really dumb question...
I've never used indexes in Laravel before and I'm a little unsure of indexing DB columns in general.
When reading the docs in indexing on laravel.com it seems to indicate that adding an index simply makes that column "unique". is that correct?
for example I would create a migration for that table and add
$table->index('user_id');
Is that correct?
Level 56
Unique indices are a particular kind of indices that does not allow duplicates.
Non-unique Indices, allow duplicates, but you are telling the DB to take the values and store them in a way that is search efficient.
If you don't want to allow duplicates you can create a unique index like this:
$table->unique('column');
For example the CreateUsersTable migration shipped with a default Laravel installation sets an unique key for the email column:
$table->string('email')->unique();
Here they are using the Fluent interface to automatically add the unique index with the column definition.
In your case, it seems you want to allow duplicates, as you want the average for the same users.
In that case you can define a non-unique index like this:
$table->index('user_id');
Or, using the fluent syntax:
$table->unsignedInteger('user_id')->index();
Take a look on the docs about DB schema definition for further insights:
Level 122
possibly consider storing cumulative plays for each user/track and then top it up each day (store total duration and total count per user/track). Use a scheduled task to adjust the totals of any tracks played n the last 24 hours.
Then when displaying the statistics is a simple job to calculate the averages
Level 39
@mick79 I agree with @rodrigo.pedra, create an index is the first thing to do and probably code sol 1 or 2 after.
It's always good to understand db in the background of Laravel before using Laravel command.
First, create or drop index will not alter your data
To create an index : CREATE [UNIQUE] INDEX <index_name> ON <table_name> (<field_name1, ...);
To see the config : DESCRIBE <table_,name>; (have a look to column key)
To cancel it : DROP INDEX <index_name> ON <table_name>;
Test with UNIQUE or Not and see the result.
Please or to participate in this conversation.