结合MySQL查询，其中一个是排除查询

Question

这里的第一个问题，所以如果我错过了以前问过的问题，或者格式不好，我很抱歉。。。。我的公司有一个自定义的CRM+数据库，我正在尝试改进。我们需要找到一份不会续签年度服务的房产清单。目前，我们通过首先查找将续订服务的属性来实现这一点，这是通过以下查询完成的：

SELECT DISTINCT 
j.`property_id` 
FROM
`jobs` AS j 
LEFT JOIN `property` AS p 
ON j.`property_id` = p.`property_id` 
LEFT JOIN `agency` AS a 
ON p.`agency_id` = a.`agency_id` 
INNER JOIN `property_services` AS ps 
ON (
j.`property_id` = ps.`property_id` 
AND j.`service` = ps.`alarm_job_type_id`
) 
WHERE ps.`service` = 1 
AND a.`country_id` = 1 
AND (
j.`status` = 'Pending' 
OR j.`date` IS NULL 
OR j.`date` = '0000-00-00' 
OR j.`job_type` = 'Once-off' 
OR j.`job_type` = '240v Rebook' 
OR (
j.`date` >= '2019-04-22' 
AND j.`job_type` = 'Yearly Maintenance'
)
)

然后我们找到我们想为用户显示的详细信息，不包括过程中的其他项目：

SELECT DISTINCT 
j.`property_id`,
p.`address_1` AS p_address1,
p.`address_2` AS p_address2,
p.`address_3` AS p_address3,
p.`state` AS p_state,
p.`postcode` AS p_postcode,
a.`agency_id`,
a.`agency_name` 
FROM
`jobs` AS j 
LEFT JOIN `property` AS p 
ON j.`property_id` = p.`property_id` 
LEFT JOIN `agency` AS a 
ON p.`agency_id` = a.`agency_id` 
INNER JOIN `property_services` AS ps 
ON (
j.`property_id` = ps.`property_id` 
AND j.`service` = ps.`alarm_job_type_id`
) 
WHERE p.`property_id` NOT IN (INSERT HERE THE IDS YOU GOT FROM THE FIRST QUERY) 
AND ps.`service` = 1 
AND p.`deleted` = 0 
AND p.`agency_deleted` = 0 
AND a.`status` = 'active' 
AND a.`country_id` = 1 
ORDER BY j.`property_id` DESC 
LIMIT 0, 50 

理想情况下，我想将这些查询组合起来，或者以某种方式优化它们，因为页面目前需要2分钟以上的加载时间，即使有索引。再次，我很抱歉，我不是数据库或查询专家，很确定这个学位只包括一两个主题！

Answer 1

对于记录(以防有人发现它有帮助(，组合版本是：

SELECT DISTINCT 
j.`property_id`,
p.`address_1` AS p_address1,
p.`address_2` AS p_address2,
p.`address_3` AS p_address3,
p.`state` AS p_state,
p.`postcode` AS p_postcode,
a.`agency_id`,
a.`agency_name` 
FROM
`jobs` AS j 
LEFT JOIN `property` AS p 
ON j.`property_id` = p.`property_id` 
LEFT JOIN `agency` AS a 
ON p.`agency_id` = a.`agency_id` 
INNER JOIN `property_services` AS ps 
ON (
j.`property_id` = ps.`property_id` 
AND j.`service` = ps.`alarm_job_type_id`
) 
WHERE p.`property_id` NOT IN 
(SELECT DISTINCT 
j.`property_id` 
FROM
`jobs` AS j 
LEFT JOIN `property` AS p 
ON j.`property_id` = p.`property_id` 
LEFT JOIN `agency` AS a 
ON p.`agency_id` = a.`agency_id` 
INNER JOIN `property_services` AS ps 
ON (
j.`property_id` = ps.`property_id` 
AND j.`service` = ps.`alarm_job_type_id`
) 
WHERE ps.`service` = 1 
AND a.`country_id` = 1 
AND (
j.`status` = 'Pending' 
OR j.`date` IS NULL 
OR j.`date` = '0000-00-00' 
OR j.`job_type` = 'Once-off' 
OR j.`job_type` = '240v Rebook' 
OR (
j.`date` >= '2019-05-08' 
AND j.`job_type` = 'Yearly Maintenance'
)
)) 
AND ps.`service` = 1 
AND p.`deleted` = 0 
AND p.`agency_deleted` = 0 
AND a.`status` = 'active' 
AND a.`country_id` = 1 
AND (
j.`status` != 'Booked' 
AND j.`status` != 'To Be Booked' 
AND j.`status` != 'Send Letters' 
AND j.`status` != 'On Hold' 
AND j.`status` != 'On Hold - COVID' 
AND j.`status` != 'Pre Completion' 
AND j.`status` != 'Merged Certificates'
) 
AND j.`date` > DATE_ADD(NOW(), INTERVAL - 350 DAY) 
ORDER BY j.`property_id` DESC

StackOverflow之外的人帮助合并，但没有帮助。。。我们最终不得不添加一个标记并搜索它，因为这个查询在我们的数据库上运行需要193秒。

此外，我完全需要提供一个最低限度的可复制示例，而我没有这样做是我的错。