Laravel Model query is slow, but fast on MySQL?-CodePudding

I have a table in my database with approx 400K rows, and I am executing the following statement (query that laravel is executing):

select * from `activities` where `device_id` = ? and `battery_level` is not null order by `created_at` desc limit 1

This takes less than 100ms when I execute it directly using a MySQL client. But laravel takes 1,5 - 2 seconds

My table looks like this:

And how I use the models: (takes 1500ms)

Activity::query()
   ->where('device_id', 288)
   ->whereNotNull('battery_level')
   ->orderByDesc('created_at')
   ->first();

And with DB: (also takes 1500 ms)

 DB::table('activities')
            ->where('device_id', 288)
            ->whereNotNull('battery_level')
            ->orderByDesc('created_at')
            ->first();

I am looking to reduce this query to the same speed as MySQL between 100 and 200ms max. Removing the ordering desc reduces the time of the query, but I need to use order desc to get the latest.

Any idea what I am doing wrong here?

CodePudding user response：

It may be helpful to provide SHOW CREATE TABLE.

Replacing INDEX(device_id) by

 INDEX(device_id, battery_leve, created_at)

may help.

CodePudding user response：

Try this:

$record = DB::raw("SELECT * FROM activities WHERE device_id = 1 AND battery_level IS NOT NULL ORDER BY created_at DESC LIMIT 1")