Optimize query with a subquery (index is not used)

Question

I have following query:

SELECT 
`status`, `is_client_coordinator`, id, first_name, last_name 
FROM `users` `t` 
WHERE t.status!="CLOSED" 
and t.is_client_coordinator 
or t.id in (select client_coordinator_id from projects where status not in ("DELETED", "ARCHIVED")) 
ORDER BY concat(first_name, last_name)

The explain gives this:

enter image description here

Individually queries take less than 0.1 seconds (first one 0.04 seconds and the subquery 0.07 seconds). Put together it takes more than a second to perform the query. Both status columns are the type of ENUM. What am I doing wrong here? Any advice about how to optimize the query?

• NikitaSerbskiy: could you please add create table statemets (including indexes)?
• Rick James: How many rows are you expecting in the result?

Answer 1

In order for the in clause to be evaluated, the subquery is likely being evaluated for every row. My first performance suggestion would be to use a left join and then filter:

select t.status, t.is_client_coordinator, t.id, first_name, t.last_name
from users t
left join projects p on t.client_coordinator_id = pl.id
where t.status != "CLOSED"
and t.is_client_coordinator
or (
exists(p.client_coordinator_id)
and p.status != "DELETED"
and p.status != "ARCHIVED"
)
ORDER BY concat(t.first_name, t.last_name)