Level 2
to make things even more mysterious, I tried using PDO and prepare a statement, and the query its also slow...
Jan 10, 2024
7
Level 2
Laravel is messing up a query. Need to run it raw
Hi all!
After many hours trying to solve a problem of a query not using spatial indexes, I've bumped into two issues.
First, the migration doesn't seem to create the table/index properly, and I need to run manual DB::statements to alter the table and the index in order for it to work. (edit: this might be the fault of the package I am using)
Secondly, when I run a SQL statement directly to the DB, I see the index is used and the result is fast.
When I try to run the exact same query in the laravel app, using DB::select($sql) the same query takes around 10 seconds, which means the index is not being used.
Why/how would Eloquent change things that mess up the query? And how can I run this manually, and fetch the results in Laravel? thanks!
Level 2
and to add to that, I used debugbar just to make sure the SQL is not changed in any way. Its exactly the same as when I execute it outside the app. But in the app takes 12s and outside 0.002s :(
Level 2
ok, interesting twist of events. When running the query via DBeaver or MysqlWorkbench it works as expected (fast). If I run it in Beekeper Studio, or anything PHP related (even a simple PHP script) it is low. Could this be a driver problem?
Level 27
@nunodonato Sounds like it might be a driver issue, yes. What database are you using? Is it SQL Server, by any chance?
Level 2
@kokoshneta no, standard mysql 8
Level 2
made it work by using FORCE INDEX in the raw SQL. No idea why it needs this in some cases and not others...
Level 73
@nunodonato Sounds like you have some issues in your lemp/lamp stack, I would suggest reinstalling and try again. Adding a hint like that is not a good idea, and should be avoided.
However, Regardless if you are using the Query builder (DB::select), Eloquent (Model::query), or a straight PDO select statement, the SQL is still mostly the same, and there is something in the Database that isn't working as intended. All of the methods mentioned all boils down to SQL being ran with PDO.
Please or to participate in this conversation.