Have Geek Brain

How to optimize a MySQL query containing a subquery?

I have two tables, House

and Person

. For any row in the House, there can be 0, 1, or many matching rows in Person

. But of these people, at most one will have the status "ACTIVE", the rest will have the status "CANCEL".

eg.

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 canceled lines and get something like this:

House.ID | Person.ID | Person.Status
       1 |         3 |        ACTIVE
       2 |         1 |        ACTIVE
       3 |      NULL |          NULL
       4 |      NULL |          NULL

I achieved this with the following selection:

SELECT *
FROM House
LEFT JOIN 
(
    SELECT *
    FROM Person
    WHERE Person.Status != "CANCELLED"
) Person
ON House.ID = Person.HouseID

... which works, but breaks all indexes. Is there a better solution that doesn't?

I am using MySQL and all the relevant columns are indexed. EXPLAIN

nothing is displayed in possible_keys

.

Thanks.

+2


a source to share


4 answers


What about:



SELECT *
FROM House
LEFT JOIN Person
ON House.ID = Person.HouseID 
AND Person.Status != "CANCELLED"
+4


a source


Do you have control over the structure of your database? If so, I think you could better represent your data by removing the Status columns from the Person table and adding the ActivePersonID column to the House table instead. This removes any redundant CANCELED values ​​from Person and excludes application or stored procedure code to ensure that only one person per household is active.

Alternatively, you can submit your request as



 SELECT * FROM House LEFT JOIN Person ON House.ActivePersonID = Person.ID
+2


a source


Using:

   SELECT * 
     FROM HOUSE h 
LEFT JOIN PERSON p ON p.houseid = h.id
                  AND p.status = 'ACTIVE'
+1


a source


This is in SQL Server, but the logic seems to work, echoing Chris above:

declare @house table
(
    houseid int
)

declare @person table
(
    personid int,
    houseid int,
    personstatus varchar(20)
)

insert into @house (houseid) VALUES (1)
insert into @house (houseid) VALUES (2)
insert into @house (houseid) VALUES (3)
insert into @house (houseid) VALUES (4)

insert into @person (personid, houseid, personstatus) VALUES (1, 1, 'CANCELLED')
insert into @person (personid, houseid, personstatus) VALUES (2, 1, 'CANCELLED')
insert into @person (personid, houseid, personstatus) VALUES (3, 1, 'ACTIVE')
insert into @person (personid, houseid, personstatus) VALUES (1, 2, 'ACTIVE')
insert into @person (personid, houseid, personstatus) VALUES (4, 4, 'CANCELLED')

select * from @house
select * from @person

select *
from @house h LEFT OUTER JOIN @person p ON h.houseid = p.houseid 
    AND p.personstatus <> 'CANCELLED'
0


a source






More articles:


All Articles