mysql query with 1 derived table and 1 inner join takes forever to load

Question

I tried removing some chunks of code little by little and found out that the condition inside the inner join is the devil. Can someone enlighten me on how to achieve this when I have like 10,000+ rows of data. This works perfectly fine if I have 1-50 row of data

SELECT negotiations.id
FROM
(SELECT n.id,n.business_rank,db.status,
case status
        when '11' then IF(DATEDIFF(IFNULL(apply_date, curdate()), curdate()) < 1, true, false)
        when '12' then IF(DATEDIFF(IFNULL(db.contract_date, curdate()), curdate()) < 1, true, false)
        when '13' then IF(DATEDIFF(IFNULL(db.settlement_date, curdate()), curdate()) < 1, true, false)
        when '20' then IF(DATEDIFF(IFNULL(db.apply_cancel_date, curdate()),  curdate()) < 1, true, false)
        when '21' then IF(DATEDIFF(IFNULL(db.contract_cancel_date, curdate()),  curdate()) < 1, true, false)
end as to_notify
FROM negotiations AS n
INNER JOIN deal_buildings AS db ON db.id = (
    SELECT id
    FROM deal_buildings AS db2
    WHERE db2.deal_id = n.id
    AND n.main_member_id = 79
    AND db.status in (11,12,13,20,21)
    ORDER BY status DESC
    LIMIT 1
) ) as negotiations
WHERE ((
(business_rank = '17' and status = 11) or 
(business_rank = '19' and status = 12) or 
(business_rank = '22' and status = 13) or
(business_rank = '18' and status = 20) or
(business_rank = '20' and status = 21)) is false )
and to_notify

score 0 · Answer 1 · answered Apr 23 '19 at 04:09

(Need formatting not available in a Comment.)

This is ambiguous:

FROM deal_buildings AS db2
WHERE db2.deal_id = n.id
  AND n.main_member_id = 79
  AND db.status in (11,12,13,20,21)
ORDER BY status DESC              -- Is this in `db` or `db2`?  QUALIFY!

IF(blah, true, false) can be shortened to just (blah)

mysql query with 1 derived table and 1 inner join takes forever to load

1 Answers1