MySQL Forums
Forum List  »  Performance

Timeout of Simple Query - Makes no sense
Posted by: David Allen
Date: July 06, 2023 10:05AM

We have a system that builds dynamic SQL based on selections made by the front-end. It works great (overall), but there appears to be an issue with what should be a simple query that times out.

Here we go:

-- This takes 20ms, 4800 records
SELECT phw.person_main_ref_id AS ID
FROM person_history_work phw
WHERE MATCH(phw.work_summary) AGAINST('finance');

-- This takes 20ms, 24000 records
SELECT phw.person_main_ref_id AS ID
FROM person_history_work phw
WHERE MATCH(phw.work_title) AGAINST('software');

-- This takes 700ms, returns 14000 records
SELECT pm.ID
FROM person_main pm
WHERE
pm.ID IN
(
SELECT phw.person_main_ref_id AS ID
FROM person_history_work phw
WHERE phw.ID < 50000
)
AND
pm.ID IN
(
SELECT phw.person_main_ref_id AS ID
FROM person_history_work phw
WHERE phw.ID > 10000
);

-- This times out at over 60000ms, should return at max 4800 records
SELECT pm.ID
FROM person_main pm
WHERE
pm.ID IN
(
SELECT phw.person_main_ref_id AS ID
FROM person_history_work phw
WHERE MATCH(phw.work_summary) AGAINST('finance')
)
AND
pm.ID IN
(
SELECT phw.person_main_ref_id AS ID
FROM person_history_work phw
WHERE MATCH(phw.work_title) AGAINST('software')
);

I'm scratching my head on this one.The above AND compares only compare 4800 vs 2400 ID entries and independently they take less than 50ms each to execute. But put into the main query, it's more than 60000ms. It times out. Note this happens locally and on AWS.

It's obviously not an issue with the ID counts being compared, because:

-- This takes 24ms, returns 13183 records
SELECT pm.ID
FROM person_main pm
WHERE
pm.ID IN
(
-- 49838 records
SELECT phw.person_main_ref_id AS ID
FROM person_history_work phw
WHERE phw.ID < 50000
)
AND
pm.ID IN
(
-- 299679 records
SELECT phw.person_main_ref_id AS ID
FROM person_history_work phw
WHERE phw.ID > 10000
);

So any idea what's going on here? Seems like it could be a bug or some serious issue with the engine optimization.

Options: ReplyQuote


Subject
Views
Written By
Posted
Timeout of Simple Query - Makes no sense
238
July 06, 2023 10:05AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.