Looping through MySQL Query progressively gets slower I have a MySQL Query, which as part of a batch job, loops through about 4,000 records - running the same queries against each of the 4,000 records.

As it moves through the records, it gets progressively slower.

By the time it's at the 4,000th, it's taking up to 20 times longer than when it did for the first loop.

There's actually 10 queries which run in each loop (SELECT), but the overall trend is all getting slower as the batch job progresses.

Also interesting is comparing how long each of the 10 individual queries takes in the loop.

At the start of the batch it's uneven (e.g. one SELECT takes 1% of loop time, whilst another takes 35% of loop time for example). This is as expected, as some queries are bigger than others.

But as it progresses, these 10 queries all start to take a much more similar time, all close to 10% of loop time -- suggesting perhaps there is a delay building up and affecting statements before they execute?

On my dev environment, MySQL is running locally - Running MySQL v5.7.24 on Ubuntu 16 on AWS EC2.

On my live environment,  I use AWS RDS. Running Aurora MySQL v5.6.10 on Ubuntu 16 on AWS EC2. I'm currently using Aurora Serverless, so have limited config control, but can move to RDS MySQL instance if there's config I need to access which would help.

The issue occurs in both environments.

It feels like previously executed statements are hanging around in the memory and not full freeing up resources for the next loop. But I don't know where to start looking to rectify.

EDIT:

On my dev environment (with local MySQL server), the load gets pretty high during the process (close to 100%), with RAM and SWAP not really changing (and well within limits).

On my live environment (RDS MySQL), the load and RAM are consistent, with no real change during the process (and well within limits), but the RDS  hits around 60% CPU utilization

So I'm obviously peaking out the CPU - which then, in turn, results in a lag with flows on?

EDIT:

I just doubled my dev environment EC2 (t2.small to t2.medium), which doubles RAM and 1CPU -> 2CPU. It's made no difference, and I notice running htop that only one of the CPUs is maxing out -- the other is almost idle - and I've just read that the MySQL Process won't (can't) be shared across multiple cores... so that doesn't help :-)

Welcome any thoughts and advice,

Thanks
SOLVED:

Found one statement involving a table which was missing a crucial INDEX, and it was bogging down there.

Adding the INDEX and each loop is now pretty consistent - no progressive lag.

Re query "can't you do all the work in a single JOIN between the two tables, rather than 1+4000 queries" - there's a heap more going on in each loop (sending messages etc) so it's more than just a MySQL Statement.

Cheers