mysql query with 1 derived table and 1 inner join takes forever to load
0
votes
1
answer
93
views
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
Asked by usc_student
(1 rep)
Apr 12, 2019, 10:00 AM
Last activity: Apr 23, 2019, 04:09 AM
Last activity: Apr 23, 2019, 04:09 AM