MySQL Hangs

SELECT tc_positions.* FROM tc_positions WHERE id IN (SELECT positionId FROM tc_devices)

I am using AWS RDS and not sure, what api / function is calling above query, which hangs MYSQL.

The CPU utilization rises to 100% and AAS goes beyond 300

Maybe AWS is using an ancient version of MySQL that didn't know how to handle this query?

Any suggestions I should look into setting any parameters. I am using mysql 8.0.33

You need to check the query execution plan to see why it takes so long. It's probably not using indexes for some reason.

https://drive.google.com/file/d/1-D8Bk7bYnKTHNcYXGwXeooWy6T1sl7O1/view?usp=sharing

I ran explain statement

It looks good to me. I don't understand why it would hang on it though. Either this is not the query that it hangs on or there's some serious problem with your database.

How about

Select tc_positions.* from tc_positions
Join tc_devices on id = positionid

This requires code level change ? As we are not doing this query

Could you use a mysql utility to run and compare the two?

Also I've seen it suggested that max_allowed_packet may need increasing for large IN clauses.

appreciate your response the result of this is faster compared to the original query

Select tc_positions.* from tc_positions Join tc_devices on tc_positions.id = positionid

i have increased the max_allowed_packet to 32 MB from default 16 MB. But in query execution level it didnt made any change

additionally, if you can please guide which api is using this query, it may help to drill down the cause

Can I do this query change in default.xml ?? Id yes how