Laravel/MySQL: Very high query runtime

In my laravel project I am currently doing some testing with database seeders to check the performance with many database entries. In my project I have a Model FlightView using a SQL view as database by doing protected $table = 'view_flights';.

During testing I realized that queries like FlightView::where('aircraft_id',1)->get() are getting very slow (2 or 2.5 seconds per call). I am evaluation the query runtime in laravel with Laravel Debugbar.

I realized, that the underlying query is handled differently when filtering on one aircraft id (runtime 2 to 2.5 seconds), but is very fast when filtering on two or more aircraft id (runtime around 100 ms). By checking the underling view query I did some optimizations by replacing a LEFT JOIN into a INNER JOIN, so that in both cases a "ref" (index lookup) is being used (as described here).

After updating the view, the runtime within my SQL editor became 50 to 100 ms. However, within laravel with exactly the same queries are still enormously long:

FlightView::where('aircraft_id',1)->get()
Runtime: 2.23 sec
Query: select * from `view_flights` where `aircraft_id` = 1

I tried lots of variations as you can see in the following results, but I didn’t get a clue what is going wrong or what I could do next…

FlightView::whereIn('aircraft_id',[1])->get()
Runtime: 2.24 sec
Query: select * from `view_flights` where `aircraft_id` in (1)

To exclude any Eloquent issues, I tried the same with the DB Facade, but it’s still slow.

DB::table('view_flights')->where('aircraft_id',1)->get()
Runtime: 2.22 sec
Query: select * from `view_flights` where `aircraft_id` = 1

Next I tried to DB::select() statement, but also no improvement:

DB::select('select * from view_flights where aircraft_id=?',[1]);
Runtime: 2.22 sec
Query: select * from `view_flights` where `aircraft_id` = 1

Without bindings, the query speed increased a lot:
Next I tried to DB::select() statement, but also no improvement:

DB::select('select * from view_flights where aircraft_id=1');
Runtime: 23 ms
Query: select * from view_flights where aircraft_id = 1

Surprisingly, when filtering on two aircraft ids, the laravel query is still very fast:

FlightView::whereIn('aircraft_id',[1,2])->get()
Runtime: 53 ms
Query: select * from `view_flights` where `aircraft_id` in (1,2)

Next, I let laravel try to explain, why it is so slow:

DB::select('explain select * from view_flights where aircraft_id=?',[1]);
Runtime: 2.22 sec
Query: explain select * from `view_flights` where `aircraft_id` = 1

So Laravel seems still to do the "slow" query approach, not the "ref" (index lookup) approach 🙁

Speculations/open questions?

• Is the problem a laravel specific or a SQL-specific problem?
• Are there any MySQL settings, which may lead to the different behaviour?
• What could be the reason, that laravel handles a query different than my sql editor?
• Is there same kind of "SQL view"-caching or in Laravel, which might still use the old version of the view?
• Is there any "magic" within laravel which might explain this behaviour?
• What could I do next?