Level 39
Which platform are you using?
Apr 19, 2022
31
Level 1
First query really slow
I am currently having an issue where my Laravel app is really slow at loading when navigating. I checked Clockwork and messed around with different queries and have noticed that the first query on any page is 10x slower than if it is called later.
Has anyone encountered this issue before?
Level 1
@sr57 I am running the application locally and connecting to a MYSQL server (I am using ssl certs, unsure if that is having an impact?)
Level 39
Difference can be due by cache
But what do you mean by really slow?
Can you give number and an example of the a simple query.
Level 1
@sr57 When Middleware Auth is used, this is the order the queries run:
SELECT * FROM 'users' WHERE 'id' = 26 LIMIT 1; (208.91 ms)
SELECT * FROM 'clients' WHERE 'id' = 32070107 LIMIT 1; (33.47 ms)
When I remove Middleware Auth, this is the order the queries run:
SELECT * FROM 'clients' WHERE 'id' = 32070107 LIMIT 1; (209.66 ms)
SELECT * FROM 'users' WHERE 'id' = 26 LIMIT 1; (31.05 ms)
Level 73
@AdamSims Can you run this sql statement and share the result.
EXPLAIN SELECT * FROM 'users' WHERE 'id' = 26 LIMIT 1;
Level 39
don't seem to be a pb linked to the query / table structure
Level 1
@Tray2 First Query:
[{"id":1,"select_type":"SIMPLE","table":"users","partitions":null,"type":"ALL","possible_keys":null,"key":null,"key_len":null,"ref":null,"rows":26,"filtered":100,"Extra":null}]
Second Query:
[{"id":1,"select_type":"SIMPLE","table":"users","partitions":null,"type":"ALL","possible_keys":null,"key":null,"key_len":null,"ref":null,"rows":26,"filtered":100,"Extra":null}]
The first one took 194.3ms and the second took 30.59ms
Level 73
@AdamSims The id column in your users table isn't a primary key like it should be. That is probably the main reason for it to be slow.
The result of your explain should look like this
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 8 | const | 1 | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.000 sec)
Level 73
@sr57 The issue is related to missing primary key on the users table, see my reply below.
Level 39
Full agree with the key but strange that conducts to the OP problem, let's see ...
Level 1
@Tray2 while this would make sense, that cant be the issue I am having as when I run the exact same query but second (for example I run the Clients query then the Users one second) the time taken to execute reduces from ~190ms to ~30ms
It doesnt matter what I am querying, the first query ran on a page always takes much longer.
Level 73
@AdamSims That is most likely because the second time the query has been cached by the database and therefore runs faster.
Level 39
What's the code that make the queries don't run in the same order? (should be nor normal?) - but don't see now a relation with your question.
Level 1
@sr57 The way I changed the order was to remove middleware('auth') from the route, by doing this it triggered the User query second as opposed to first.
This revealed that no matter what query is being ran, its always the first one that takes much longer to process.
Level 39
Could you share your routes file (web.php)?
Level 122
is the database server local or over the network?
Level 1
@Snapey the Database server is external and I am connecting to it with SSL certificates (maybe that is the issue?)
The connection is fine after the first execution. Its just the very first one...
Level 122
@AdamSims or dns resolution.
Level 1
@Snapey I am connecting using the mysql server IP, would that still cause a DNS resolution issue?
Level 75
@AdamSims If you go to the search here on forum, and type in javascript, the results aren't instant. However if done a second time it seems to to show instantly. But I am not sure, but your results may be normal.
Level 1
I'm having the exact same problem with Eloquent, first query is slow, takes 170+ms, while other queries (exactly same as the first one) take about 35ms.
@adamsims did you get any solution for this?
Level 6
Bumping. Also experiencing this issue. No matter what query is first (even a super simple query that takes a couple ms directly in mysql) is taking more than 300ms. Tried it locally and on a server. Is it due to overhead in establishing a DB connection?
To elaborate, this is how I'm testing...
Route::get('/', function () {
DB::select("SELECT id from table where id = 2");
DB::select("SELECT id from table where id = 4");
return view('welcome');
});
Looking at the DB events in DebugBar, it looks like the first StatementPrepared event is what's taking the chunk of time. Not sure how to interpret the duration of an event (is it the construction of the event?), but will do some more investigating around what happens there...
Illuminate\Database\Events\StatementPrepared (423ms)
Illuminate\Database\Events\QueryExecuted (55.43ms)
Illuminate\Database\Events\StatementPrepared (41.36ms)
Illuminate\Database\Events\QueryExecuted (39.48ms)
Any help/insight appreciated. Thanks!
Level 1
I'm having the same issue using Laravel + PostgreSQL. Maybe these delays are due to the first time a PHP script is opening a DB connection?
In my case, no matter the initial query, the first script normally takes up to ~70% of total time (10 queries, first one takes 70ms from a total of 96ms)
1 like
Level 1
Having the same issue on first query (even tested with many simple queries), did you get any solution @adamsims?
Level 7
Exactly the same problem First query to remote db ran much slower.
SELECT * FROM "ExpenseManager_client"
WHERE "ExpenseManager_client"."id" = 2546
LIMIT 1 (Time: 1205.85)
SELECT * FROM "ExpenseManager_client"
WHERE "ExpenseManager_client"."id" = 639
LIMIT 1 (Time: 238.8)
---------------
Duration: 1.4510719776154s
Memory Usage: 32.5MB/32.5MB
Is there a chance to get out of this trouble?
Level 54
@simcha starting a new thread for your question will get a better chance of getting it answered.
Level 73
@simcha If you read the replies to this thread, run the query with EXPLAIN i n front of it, it is most likely that you don't have an primary key index on the id column.
Level 5
@simcha In the first query, it is making a connection to DB. That's why it is slow.
1 like
Level 1
Any news? @sofia
Level 6
@Wit3 Not really. It came up as I was converting a PHP (no framework) API to Laravel and was doing some performance analysis.
I do believe it's a Laravel thing but I didn't dig deep enough to figure out exactly what it was. I didn't see the same first query delay using PDO directly, so not exactly due to establishing a connection.
Overall I was able to significantly improve performance so I moved on from trying to understand this detail, but would still be very curious if anyone has been able to isolate it.
Level 1
I believe this is actually the "boot cost".
Normally, Laravel (like most PHP apps) works like this:
- A request comes in.
- PHP-FPM spins up a fresh process.
- Laravel boots up (autoloads classes, registers service providers, reads config, builds containers, etc.).
- It handles the request.
- The process dies.
This means every request pays the boot cost (framework init + DB connection + config load).
Which in our case is reflected in the first query - if you look at queries. If you look (in the Laravel debugger) at the Timeline, you'll see that the "Booting" phase takes usually ~50% of the total time.
The solution in this case is to use a supercharged solution like Octane, which will preload in memory all laravel's setup and keep db connections open.
Please or to participate in this conversation.