Simple query slow in Laravel, but insanely fast in database console

Question

I have a very strange problem, that I cannot get my head around. I am using Laravel for my backend application, where I am running a very simple query on table with 30k records all with proper indexes on it. Here is the query:

DB::select('select * from Orders where ClientId = ?', [$id])

From the Laravel application this query runs for 1.2 seconds (The same thing is if I use Eloquent model.):

    "query" => "select * from Orders where ClientId = ?"
    "bindings" => array:1 [▼
      0 => "44087"
    ]
    "time" => 1015.2

The problem is, if I run THE SAME query inside the database console or PHPMyAdmin, the query takes approximate 20miliseconds.

I do not understand how is that possible since I am using the same database, same query, same computer and same connection to the database.

What can be the reason?

Answer 1

I would try debug the queries with the Debug Bar, to see how much time it takes, and which is taking longer,... It's very easy to use and install: https://github.com/barryvdh/laravel-debugbar I think you are interested in DB administrations.. read this also,you can get some idea.good luck

Answer 2

PHPMyAdmin will automatically add LIMIT for you.

This is because PHPMyAdmin will always by default paginate your query.

In your Laravel/Eloquent query, you are loading all 30k records in one go. It must take time.

To remedy this try pagination or chunking your query.

The total will take long, yes, but the chunks themselves will be very quick.