Mysql – Optimize query with a subquery (index is not used)

MySQLoptimizationsubquery

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?

Best Answer

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)
Related Question