How to optimise MySQL query containing a subquery?
- by aidan
I have two tables, House and Person. For any row in House, there can be 0, 1 or many corresponding rows in Person. But, of those people, a maximum of one will have a status of "ACTIVE", the others will all have a status of "CANCELLED".
e.g.
SELECT * FROM House LEFT JOIN Person ON House.ID = Person.HouseID
House.ID | Person.ID | Person.Status
1 | 1 | CANCELLED
1 | 2 | CANCELLED
1 | 3 | ACTIVE
2 | 1 | ACTIVE
3 | NULL | NULL
4 | 4 | CANCELLED
I want to filter out the cancelled rows, and get something like this:
House.ID | Person.ID | Person.Status
1 | 3 | ACTIVE
2 | 1 | ACTIVE
3 | NULL | NULL
4 | NULL | NULL
I've achieved this with the following sub select:
SELECT *
FROM House
LEFT JOIN
(
SELECT *
FROM Person
WHERE Person.Status != "CANCELLED"
) Person
ON House.ID = Person.HouseID
...which works, but breaks all the indexes. Is there a better solution that doesn't?
I'm using MySQL and all relevant columns are indexed. EXPLAIN lists nothing in possible_keys.
Thanks.
