14

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?

Zaay
  • 622
  • 2
  • 8
  • 23
  • Most probably you are rendering the data in php → html which is causing the slowness. Generally, The query time will be almost same for both laravel and console. Did you try using dd($queryresult); and check the time difference. You should use pagination if there are 30k records. – Saurav Apr 07 '19 at 13:51
  • @Saurav, I am fetching only a few records, so pagination is not needed. Also, I am dumping the data with DD function, no html or php rendering. – Zaay Apr 07 '19 at 14:25
  • Can you provide these (anonymized) 30K records? – Jonas Staudenmeir Apr 13 '19 at 07:56
  • Are you sure it's the query that takes this long or could it be something else in your application? – Thomas Apr 16 '19 at 15:25
  • @Thomas this is it, because i have empty Laravel application, with only this query running. – Zaay Apr 17 '19 at 16:50
  • Show the schema for this table, please. – Styx Apr 17 '19 at 19:51
  • My guess would be the results were cached in MySQL when you tried it in PMA. I would try executing the query 2 times in a row in your Laravel app, log both queries and see how long the 2nd one took (my guess would be a lot less than the 1st one). Still, the difference is huge – DevK Apr 18 '19 at 03:15
  • You could try disabling the cache in your PMA query: `SELECT SQL_NO_CACHE * FROM Orders where ClientId = 44087` – Thomas Apr 18 '19 at 10:58
  • Could you show how you execute the query in PMA? – Dharman Apr 18 '19 at 19:22
  • How did you measure the time in laravel? – Paul Spiegel Apr 19 '19 at 11:00
  • have a same problem after migration to another server... Laravel showing me 600ms for query... phpmyadmin 0.0001 sec. And on old server mysql queries is fast on both – Oleksii Semeniuk Oct 21 '19 at 14:18

6 Answers6

6

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.

Unamata Sanatarai
  • 6,475
  • 3
  • 29
  • 51
3

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

user11324665
  • 94
  • 2
2

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.

Dragas
  • 1,140
  • 13
  • 29
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.

ICE
  • 1,667
  • 2
  • 21
  • 43
  • 1
    no unfortunately not.. I am using the EXACT same query in laravel application as I do in phpmyadmin. Phpmyadmin returns results in 100ms, Laravel query in 1.2 seconds. – Zaay Apr 12 '19 at 17:07
  • @Zaay I know that, but PHPMyAdmin adds that limit automatically even if you don't use that at the end of the query. – ICE Apr 12 '19 at 18:02
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.
gaRex
  • 4,144
  • 25
  • 37
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

Creative87
  • 125
  • 9