I have a call to an endpoint that returns a paginated set of results (15 at a time). To get all the information needed, I am running between 60 - 100 queries. I realise this is a lot and I am optimizing this seperately, but I really want to get to the source of my issue!
Using laravel devbug bar, I can see all queries are fast apart from a single
sum where. As part of the page load this take 200ms and runs 29 times. If I take the exact SQL query and run this directly in MySQL or as just a single query within Laravel, the run time is no more than 10ms.
I have profiled the request within BlackFire on a production server with plenty of free memory and CPU.
Run time: 9s Peak Memory: 1.68mb Queries: 98
The slow query is the same as the one as shown in debugbar and takes a total of 9.07s over 29 queries.
What could be causing this query that takes only 10ms to run on its own, to now take over 9 seconds to run 29 times?
For reference, the "slow" query is:
select -SUM(picked) as packed, `product_id` from `stock_movements` where `stock_movements`.`location_id` = '700020241' and `stock_movements`.`location_id` is not null and `stock_movements`.`company_id` = '8b11050c-612c-4922-8b34-d04d579e02a9' group by `product_id`