MySQL: From sub query to a single query

Question

I have this query which i believe can be optimized:

SELECT floors.id, floors.floor FROM floors
WHERE floors.societies_id = 1
AND floors.status = 'Y'
AND floors.id NOT IN (
    SELECT DISTINCT(floors.id) FROM floors
    INNER JOIN societies ON societies.id = floors.societies_id
    INNER JOIN resident_floors ON resident_floors.floors_id = floors.id
    WHERE societies.id = 1
    AND floors.status = 'Y'
)

Is this query fine to use or there it can be improved..?

You should create a SQL fiddle. and/or add data-structure to kick helpers on it. — Bilal Siddiqui, Oct 15 '19 at 05:51
Ok @BilalSiddiqui, next time I'll definitely keep that in mind :) — Mr.Singh, Oct 15 '19 at 06:09

score 1 · Accepted Answer · answered Oct 15 '19 at 05:51

1

It looks like you want to get all floors that aren't present in resident_floors. For this we can left join RF in and ask for only rows where the join failed resulting in a null in RF:

SELECT floors.* FROM floors
INNER JOIN societies ON societies.id = floors.societies_id
LEFT JOIN resident_floors ON resident_floors.floors_id = floors.id
WHERE societies.id = 1
AND floors.status = 'Y'
AND resident_floors.floors_id IS NULL

answered Oct 15 '19 at 05:51

Caius Jard

72,509
5
49
80

Thanks @CaiusJard, it worked. However, could my query be used or there would have been some performance issues..? – Mr.Singh Oct 15 '19 at 06:01
1

It's not possible for me to say; you'll have to test performance issues yourself. Personally I don't see how doing 2 joins, then preparing a unique list of IDs, then doing 2 more joins then removing data that is in the list of calculated IDs can be faster than just straight out doing 3 joins and filtering, but who knows how MySQL optimizer will run your query? YOu have to test it – Caius Jard Oct 15 '19 at 09:59
Thank you for explaining, I didn't thought it that way. Thanks again :) – Mr.Singh Oct 15 '19 at 10:18

MySQL: From sub query to a single query

1 Answers1