Slow queries due to driver's emulate prepare

[nick-potts]

Description:
There's a significant performance issue when executing a specific query using the Laravel driver for SingleStore. The query runs much slower compared to executing it directly through the admin panel/TablePlus.

Steps to Reproduce:

1. Set up the environment using the repository: https://github.com/nick-potts/laravel-verbs-singlestore
2. Run php artisan app:seed-events to seed the database
3. Execute the following query using the Laravel driver:

   select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\\States\\CustomerState' order by `id` asc limit 1000

Expected Behavior:
The query should execute quickly, similar to when run directly in SingleStore Studio or CLI.

Actual Behavior:

• The query takes approximately 3.5 seconds to complete when run through the Laravel driver.
• The issue persists in both production and local Docker SingleStore environments.

Additional Observations:

1. The performance issue only occurs when using the Laravel driver.
2. Changing the query from order by `id` asc to order by id asc (removing backticks) reduces execution time from 3.5s to 4ms.
3. Removing the asc (despite the table being asc ordered by default) also fixes it.
4. Dumping and re-importing the table does not reproduce the issue, suggesting a possible sharding/segment related problem.

Environment:

• SingleStore version: 8.7.3 (local), 8.5.5 (production)

Additional Notes:

• Enabling auto profiling seems to "fix" the issue, with query execution time dropping to 20ms.
• The issue appears to be more pronounced under concurrent load, potentially due to CPU constraints.

[JackEllis]

@nick-potts Is this happening every run for that query? I'm tempted to pull this down and take a look as I'm sure this would be impacting us too.

[nick-potts]

@JackEllis yes. However I couldn't get it to reproduce on Singlestore Cloud, only self hosted and docker.

[AdalbertMemSQL]

Hey
I was trying to reproduce this issue locally.
I used singlestore-dev docker image to run SingleStore.
Version of SingleStore:

MySQL [laravel]> select @@memsql_version;
+------------------+
| @@memsql_version |
+------------------+
| 8.7.3            |
+------------------+

I generated 2m+ rows.

MySQL [laravel]> select count(*) from verb_state_events;
+----------+
| count(*) |
+----------+
|  2010005 |
+----------+

Tha performance of all queries is approximately the same

Broken query: 25 ms
Fast query: 29 ms
Fast on 8.5.5, slow on 8.7.3 query: 28 ms

select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` asc limit 2
24.85
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` limit 2
28.96
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by id asc limit 2
27.73

Broken query: 25 ms
Fast query: 25 ms
Fast on 8.5.5, slow on 8.7.3 query: 23 ms

select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` asc limit 2
24.98
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` limit 2
24.59
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by id asc limit 2
23.23

Broken query: 29 ms
Fast query: 27 ms
Fast on 8.5.5, slow on 8.7.3 query: 27 ms

select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` asc limit 2
28.95
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` limit 2
27.44
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by id asc limit 2
26.92

[AdalbertMemSQL]

I tried to repeat this testing with the Cloud version of SingleStore.

Sometimes first query works much slower

Broken query: 5,396 ms
Fast query: 232 ms
Fast on 8.5.5, slow on 8.7.3 query: 204 ms

select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` asc limit 2
5395.8
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` limit 2
231.78
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by id asc limit 2
204.17

Sometimes it is not

Broken query: 488 ms
Fast query: 286 ms
Fast on 8.5.5, slow on 8.7.3 query: 314 ms

select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` asc limit 2
487.68
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` limit 2
285.47
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by id asc limit 2
313.92

I tried to change the order of query executions and got situations where "Fast" query works much slower

Fast query: 5,226 ms
Broken query: 35 ms
Fast on 8.5.5, slow on 8.7.3 query: 35 ms

select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` limit 2
5225.57
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` asc limit 2
35.27
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by id asc limit 2
34.9

On the next execution

Fast query: 231 ms
Broken query: 41 ms
Fast on 8.5.5, slow on 8.7.3 query: 38 ms

select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` limit 2
230.2
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` asc limit 2
40.86
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by id asc limit 2
37.38

And also

Fast on 8.5.5, slow on 8.7.3 query: 5,234 ms
Broken query: 36 ms
Fast query: 35 ms
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by id asc limit 2
5233.44
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` asc limit 2
36.18
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` limit 2
35.1

So, in my case, the performance difference is caused by query order (the first query is slower than others) and not by the query itself.

[AdalbertMemSQL]

I executed \DB::connection()->getPdo(); before running the queries to ensure the database connection was established in advance.

With this change, all queries took approximately the same amount of time:

Broken query: 37 ms
Fast query: 36 ms
Fast on 8.5.5, slow on 8.7.3 query: 36 ms

select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` asc limit 2
36.44
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by `id` limit 2
36.3
select * from `verb_state_events` where `state_id` = 196875813868675074 and `state_type` = 'App\States\CustomerState' order by id asc limit 2
36.04

So it seems the initial slowness in my earlier tests was caused by connection initialization rather than the query itself.

[nick-potts]

I'm going to try replicate it again. On local with 8.7.3 I'm not seeing the issue.

[fgilio]

In case it helps. I tested this a while back on v8.7.x and could not replicate it, all queries took equivalent time

[AdalbertMemSQL]

I'm closing this issue as I’m unable to reproduce it.
Please feel free to reopen it if you encounter the problem again.