phpmyadmin Question posted in
The official documentation can be found here.

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

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?

Tags:

6

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    I’m almost sure this is due to the using limit by PHPMyAdmin, related to what you are seeing in the page output.

    If you see top of the PHPMyAdmin page you see something like this:

    Showing rows 0 – 24 (314 total, Query took 0.0009 seconds.)

    You should have the same performance when you add the limit to your query.

    Login or Signup to reply.
  3. 0
    1. How to enable MySQL Query Log?
    2. Run query through phpmyadmin.
    3. See which queries you actually have in MySQL.
    4. Run app.
    5. See which queries you actually have in MySQL.
    6. Tell us, what was those extra, that slows down.
    Login or Signup to reply.
  4. 0

    There are several issues here. First one is how laravel works. Laravel only loads services and classes that are executed during your script. This is done to conserve resources, since PHP is meant to be run as a CGI script instead of a long running process. As a result, your timing might include the connection setup step instead of just executing the query. For a more “reliable” result, execute any query before timing your simple query.

    There’s another side of that behavior. In long running process, like Job runner, you ought not to change service parameters. This can cause undesired behavior and cause your parameter changes spill into other jobs. For example, if you provide SMTP login feature, you ought to reset the Email Sender credentials after sending the email, otherwise you will come into an issue where a user who doesn’t use that feature will send an email as another user who does. This comes from thinking that services are reloaded every time a job is executed, as such is a behavior when running HTTP part.

    Second, you’re not using limit. As some other posters pointed out.

    Login or Signup to reply.
  5. 0

    Query should be have the same speed in phpmyadmin or else whatever was the application try to use explain statement to see more details about query

    Cause of this conflict may be due to many reasons other than MySQL as example

    The php script itself have some functions that causes slow loading
    Try to check server error.log maybe there’s errors in functions

    Basically phpmyadmin could have different than larval in the MySQL connection function try to check extension used in connection maybe it’s not compatible with php version you use and I think this is the cause of slow query

    I have noticed that in some app I have made and the cause was always in the php functions or in connection as example mysql_connect was much faster than PDO exten on php < 5.6 as I experienced but cause was always from php functions in the script

    Login or Signup to reply.
  6. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search