Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hi, I currently have the following two tables
devices which many columns but the one parameter being used in the where clause is imei. This column is set to unique().
device_logs which has other fields but the parameter being used in the where clause is device_imei.
Currently I have around 50 entries in the devices table and ~128,000 entries in the device_logs table. Average execution time for a query like the below is around 150ms which becomes very significant for 50 devices.
$lastLog = $this->hasMany('App\Models\DeviceLog', 'device_imei', 'imei')->orderBy('happened_at','desc')
->where('event_id', 16)->first();
What should I be looking at to improve the execution time? Should I index the device_imei in device_logs perhaps? Executing the same raw SQL query in phpmyadmin takes a similar time of 150ms.
Thanks!
@lukegalea16 First of all, I may not go with your current structure because you manipulate the relationship concept. I prefer to write a separate method to get the first entry with some conditions from the relationship.
Then I suggest you generate a few million records locally to check the performance of your code.
In addition, you install Laravel Debugbar to check where you need to improve.
First of all, I may not go with your current structure because you manipulate the relationship concept. I prefer to write a separate method to get the first entry with some conditions from the relationship.
You mean use $this->logs()->orderBy('happened_at','desc')->where('event_id', 16)->first(); for example?
@lukegalea16 Yes, exactly.
@tisuchi Yes I am using this method in other cases - I need to update my code but thanks for the heads up.
yes of course index the device_imei column
you should also implement https://laravel.com/docs/9.x/eloquent-relationships#has-one-of-many
@Snapey indexing hugely improved the time to execute the command - thanks.
Is latestOfMany faster than sortBy and first ? Can I apply it to another column such as happened_at instead of the created_at?
Please or to participate in this conversation.