Timeout of Simple Query - Makes no sense

[David Allen]

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.