优化多表 MySQL/PHP 的连接查询

Question

我想合并下面的查询以减少执行时间，总共有 10 个表我想从中获取数据，第 11 个表是我可以执行JOIN的表。 我在每个表中都有datetime列，我想根据该datetime列获取数据，仅从该表中获取与给定日期条件匹配的数据。

$qry1 = "SELECT m.fullName, l.loanAmount AS loanAmount, l.loanStartDate FROM tblMember m
LEFT JOIN tblLoan l ON m.memberNumber = l.memberNumber
WHERE l.loanStartDate = '$date' AND l.is_active!=2";
$qry1_1 = "SELECT m.fullName, li.installment_amount, li.interest_amount , li.installment_added_date, li.installment_comment FROM tblMember m
LEFT JOIN tblLoan l ON m.memberNumber = l.memberNumber
LEFT JOIN tblLoanInstallments li ON l.id = li.loan_id
WHERE li.installment_added_date = '$date' AND li.status=1";
$qry2 = "SELECT m.fullName, f.fdAmount, f.fdStartDate FROM tblMember m
LEFT JOIN tblFD f ON m.memberNumber = f.memberNumber
WHERE  f.fdStartDate = '$date' AND f.is_active = 1";
$qry2_2 = "SELECT m.fullName, fi.installment_amount, fi.interest_amount, fi.installment_added_date, fi.installment_comment FROM tblMember m
LEFT JOIN tblFD f ON m.memberNumber = f.memberNumber
LEFT JOIN tblFDInstallments fi ON f.id = fi.fd_id
WHERE fi.installment_added_date = '$date' AND fi.status != 2";
$qry3 = "SELECT m.fullName, shi.saving_amount, shi.installment_amount, shi.interest_amount, shi.installment_added_date, shi.installment_comment FROM tblMember m
LEFT JOIN tblShare sh ON m.memberNumber = sh.memberNumber
LEFT JOIN tblShareInstallments shi ON sh.id = shi.s_id
WHERE shi.installment_added_date = '$date' AND shi.status != 2";
$qry4 = "SELECT m.fullName, si.saving_amount, si.installment_amount, si.interest_amount, si.installment_added_date, si.installment_comment FROM tblMember m
LEFT JOIN tblOnlySaving s ON m.memberNumber = s.memberNumber
LEFT JOIN tblSavingInstallments si ON s.id = si.s_id
WHERE si.installment_added_date = '$date' AND si.status != 2";
$qry5 = "SELECT m.fullName, csi.installment_amount, csi.interest_amount, csi.installment_added_date, csi.installment_comment FROM tblMember m
LEFT JOIN tblCompulsorySaving cs ON m.memberNumber = cs.memberNumber
LEFT JOIN tblCSSavingInstallments csi ON cs.id = csi.s_id
WHERE csi.installment_added_date = '$date' AND csi.status != 2";

如何优化这些查询？我已经尝试通过连接所有表，但它执行到 6-7 分钟而不是给出错误

SELECT 
m.fullName,
l.loanAmount AS loanAmount,
l.loanStartDate,
li.installment_amount,
li.interest_amount,
li.installment_added_date,
f.fdAmount,
f.fdStartDate,
fi.installment_amount,
fi.interest_amount,
fi.installment_added_date,
shi.saving_amount,
shi.installment_amount,
shi.interest_amount,
shi.installment_added_date,
shi.installment_comment,
si.saving_amount,
si.installment_amount,
si.interest_amount,
si.installment_added_date,
si.installment_comment,
csi.installment_amount,
csi.interest_amount,
csi.installment_added_date,
csi.installment_comment
FROM
tblMember m
LEFT JOIN
tblLoan l ON m.memberNumber = l.memberNumber
LEFT JOIN
tblLoanInstallments li ON l.id = li.loan_id
LEFT JOIN
tblFD f ON m.memberNumber = f.memberNumber
LEFT JOIN
tblFDInstallments fi ON f.id = fi.fd_id
LEFT JOIN
tblShare sh ON m.memberNumber = sh.memberNumber
LEFT JOIN
tblShareInstallments shi ON sh.id = shi.s_id
LEFT JOIN
tblOnlySaving s ON m.memberNumber = s.memberNumber
LEFT JOIN
tblSavingInstallments si ON s.id = si.s_id
LEFT JOIN
tblCompulsorySaving cs ON m.memberNumber = cs.memberNumber
LEFT JOIN
tblCSSavingInstallments csi ON cs.id = csi.s_id
WHERE
(MONTH(l.loanStartDate) = '5'
AND YEAR(l.loanStartDate) = '2018'
AND l.is_active != 2)
OR (MONTH(li.installment_added_date) = '5'
AND YEAR(li.installment_added_date) = '2018'
AND li.status = 1)
OR (MONTH(f.fdStartDate) = '5'
AND YEAR(f.fdStartDate) = '2018'
AND f.is_active != 2)
OR (MONTH(fi.installment_added_date) = '5'
AND YEAR(fi.installment_added_date) = '2018'
AND fi.status = 1)
OR (MONTH(shi.installment_added_date) = '5'
AND YEAR(shi.installment_added_date) = '2018'
AND shi.status = 1)
OR (MONTH(si.installment_added_date) = '5'
AND YEAR(si.installment_added_date) = '2018'
AND si.status = 1)
OR (MONTH(csi.installment_added_date) = '5'
AND YEAR(csi.installment_added_date) = '2018'
AND csi.status = 1)

WHERE查询中的条件不同，因为以前我给出的截止日期是开始日期，现在我想将其更改为月份。 所有表都有大约 10000 条记录，查询可以有 1000-2000 个 RESULT 行

Answer 1

闻起来像"过度规范化"。 如果辅助表中的所有(或大部分(列都存在于tblMember的所有行中，则将它们全部放在tblMember中。

此外，尽量避免不可优化的构造，例如

AND MONTH(l.loanStartDate) = '5'
AND  YEAR(l.loanStartDate) = '2018'

相反，请考虑使用DATE数据类型并执行

AND  loanStartDate >= '2018-05-01'
AND  loanStartDate  < '2018-05-01' + INTERVAL 1 MONTH

或者，如果您只需要存储年份和月份，则存储它们(如MEDIUMINT UNSIGNED或DECIMAL(6.0)：201805并使用

AND loanStartDate = 201805