Optimize query with a subquery (index is not used)
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)