Query execution time too long | Laravel | Page 1

void wadi Nov 29, 2023, 9:32 PM

#

Hi guys, how are you?

this piece of code:

$leads = app(GetCampaignLeadsAction::class)
->handle(campaign: $this->campaign)
->with('groupLeads')
->get;
);

basically does this query:

SELECT *
FROM group_lead
WHERE lead_id IN (SELECT id FROM leads WHERE campaign_id = 4462)

and the problem is the query, since I took it and threw it into the workbench.

What is happening is that depending on the number of leads that this query returns, the bank dies and returns nothing.
example: 400, 600, 700 leads, take 0.156 seconds to return the data. Now 1200, 1400 leads take 30 seconds (bank timeout) and return nothing! and there is not much difference from 1200 leads to 600, the time difference is 30x, there is something strange that I cannot identify!

leaden knot Nov 29, 2023, 9:47 PM

#

It's probably an inefficient query so the DB would have to do a lot of work, it's then timed out because it takes longer than your PHP max execution time. You'd have to investigate the query, see if you can optimize it, add appropriate indexes or perhaps even fetch less data in one go. You could first try running the query with an EXPLAIN, that would show you if the DB is doing full table scans, which indexes it uses and all that

void wadi Nov 29, 2023, 10:31 PM

#

Thank you for answering me friend. @leaden knot

I tried using this query with a limit of 10 (which would only bring 10 results) and it still didn't work...

It's strange that 600 records return in 0.1 seconds and 1200 return nothing. 2x more records and 30x more time is not enough

I try use explain now

static moss Nov 29, 2023, 11:33 PM

#

do you have indexes on lead_id and campaign_id?

void wadi Nov 30, 2023, 1:19 AM

#

@static moss hi friend! how are you?

you say this?

#

the table have id

#

result of explain

static moss Nov 30, 2023, 1:22 AM

#

are you sure this is the slow query?

#

according to your explain you have indexes (and not many rows)

is it the query that is slow, or something else?

have you tried running the query itself in your SQL client and seen the time to execute?

void wadi Nov 30, 2023, 1:27 AM

#

@static moss

yes, I got the query that eloquent was returning in telescope, it was this one:

SELECT *
FROM group_lead
WHERE lead_id IN (SELECT id FROM leads WHERE campaign_id = 5859);

and then I ran it on my sql client (workbench)

with campaign 4462, it resulted in 480 leads, and lasted 0.156 seconds.

With campaign 5859, I know it has 1200 leads, the bank used 30 seconds and then died without returning anything.

I found it strange, as there isn't much difference between the 2

I did more testing with campaigns with a larger number of leads, and also with smaller numbers.

those with larger numbers never pass, and those with smaller numbers like: 500, 600 leads always passing through in fractions of seconds

#

@static moss Wait a second, I had something in my face and I didn't see

#

@static moss

#

for some reason this query puts a limit of 1000

#

SELECT *
FROM group_lead
WHERE lead_id IN (SELECT id FROM leads WHERE campaign_id = 5859)
limit 3500;

I did this and it worked

static moss Nov 30, 2023, 1:35 AM

#

what is "the bank"

#

instead of running select *, do a select count (*) and let's see how many rows we are really talking about

#

given the indexes, the query shouldn't take very long.

30s is the default time for PHP exec timeout, so it makes me think that something is running for longer than 30s, and the fact that there are more rows is what is causing that execution to take longer. either an n+1 query loop, or some huge data payload is being downloaded

void wadi Nov 30, 2023, 1:40 AM

#

in fact what was making it run for more than 30 seconds, was the default limit that this query placed

#

here, the same query, which used to take 30 seconds, now takes fractions

all I did was set a limit of 3500

#

look

#

the problem was the default limit of 1000

static moss Nov 30, 2023, 1:51 AM

#

those are 2 different queries

#

I'm interested in the explain on the second

void wadi Nov 30, 2023, 1:57 AM

#

it's the same query

#

SELECT *
FROM group_lead
WHERE lead_id IN (SELECT id FROM leads WHERE campaign_id = 5859)

#

however this one is above, it has a default limit of 1000

#

putting a limit, such as 3500, resolved it, as it removed the default 1000

#

SELECT *
FROM group_lead
WHERE lead_id IN (SELECT id FROM leads WHERE campaign_id = 5859)
limit 3500;

static moss Nov 30, 2023, 11:13 AM

#

they are different one has a join, the other doesn't.

violet tangle Nov 30, 2023, 12:00 PM

#

Here's a relevant article about how to improve performance of queries with limit/offset. https://aaronfrancis.com/2022/efficient-pagination-using-deferred-joins

Efficient MySQL pagination using deferred joins - Aaron Francis

A slight modification to traditional offset/limit pagination that can drastically boost performance.

void wadi Nov 30, 2023, 12:59 PM

#

@static moss

Sorry my friend, I got the wrong query to compare, I was testing a query with join before.

here are the 2 queries, one with a limit of 3500 set by me, and the other with a standard limit of 100 set by the query itself

#

@violet tangle thank you my friend

static moss Nov 30, 2023, 1:10 PM

#

@violet tangle that is a really good article...some cool nuances in there I didn't know before.

#

I guess the application of that article, here, would be something like:

select * from group_lead l
join (
  select lead_id from group_lead
  where lead_id in (
       select id from leads
       where campaign_id = 5859
    ) limit 0, 1000
) l2 on l2.lead_id = l.id

violet tangle Nov 30, 2023, 2:44 PM

#

The syntax looks correct but I'm not sure if you'd get better performance with a join rather than WHERE lead_id IN (subquery)

static moss Nov 30, 2023, 2:50 PM

#

well, the takeaway from the article that I got was: the select * on the group_lead is forcing mysql to read all the data on the disk before applying the limit, since the "*" data is not included in the index.

so, by using the join and only selecting the lead id within the subquery, you're letting mysql use that index to apply the limit.

you could probably replace the join ( ... ) on ... with where lead_id in ( ... ) and get the same performance, but the "value" part of that query are the guts of the ...

#

that is, assuming that this is actually the issue

#

i've got a lot of multi-million row tables, and i've never experienced a 30x+ query slowdown because i changed the limit clauses on the query, especially when making it smaller. at least, not that i can recall anyway.

void wadi Dec 1, 2023, 3:34 AM

#

I passed the limit of 3500 and it worked, now I removed it and it's also working with the default limit of 1000... you'll understand hahaha

well... it's working now, and very fast

#Query execution time too long