php - 非常慢的 Mysql INNER JOIN

Question

此查询在我的本地开发机器上运行良好，但在远程服务器上需要很长时间，并且似乎使服务器超时并出现错误 500，是否有更好的方法来编写此 select 语句？，任何建议都会收到.

谢谢

J

<?php

$members = new WA_MySQLi_RS("members",$alpha,1);

$members->setQuery("SELECT
registration.*,
child_base_survey.*,
child_base_scas.*,
child_base_smqf.*,
parent_base_survey.*,
parent_base_ippa.*,
parent_base_eac.*,
parent_base_scas.*,
parent_base_smqf.*,
parent_base_eval.*,
user_access_level.*,
parent_one_month_survey.*,
parent_one_month_ippa.*,
parent_one_month_eac.*,
parent_one_month_eval.*,
child_three_month_survey.*,
child_three_month_scas.*,
child_three_month_smqf.*,
parent_three_month_survey.*,
parent_three_month_scas.*,
parent_three_month_smqf.*,
parent_three_month_eval.*,
cron.*
FROM registration
INNER JOIN child_base_survey ON registration.rego_parent_uid = child_base_survey.child_base_survey_uid
INNER JOIN child_base_scas ON child_base_survey.child_base_survey_uid = child_base_scas.child_base_scas_uid
INNER JOIN child_base_smqf ON child_base_scas.child_base_scas_uid = child_base_smqf.child_base_smqf_uid
INNER JOIN parent_base_survey ON child_base_smqf.child_base_smqf_uid = parent_base_survey.parent_base_survey_uid
INNER JOIN parent_base_ippa ON parent_base_survey.parent_base_survey_uid = parent_base_ippa.parent_base_ippa_uid
INNER JOIN parent_base_eac ON parent_base_ippa.parent_base_ippa_uid = parent_base_eac.parent_base_eac_uid
INNER JOIN parent_base_scas ON parent_base_eac.parent_base_eac_uid = parent_base_scas.parent_base_scas_uid
INNER JOIN parent_base_smqf ON parent_base_scas.parent_base_scas_uid = parent_base_smqf.parent_base_smqf_uid
INNER JOIN parent_base_eval ON parent_base_smqf.parent_base_smqf_uid = parent_base_eval.parent_base_eval_uid
INNER JOIN user_access_level ON parent_base_eval.parent_base_eval_uid = user_access_level.user_access_level_uid
INNER JOIN parent_one_month_survey ON user_access_level.user_access_level_uid = parent_one_month_survey.parent_one_month_survey_uid
INNER JOIN parent_one_month_ippa ON parent_one_month_survey.parent_one_month_survey_uid = parent_one_month_ippa.parent_one_month_ippa_uid
INNER JOIN parent_one_month_eac ON parent_one_month_ippa.parent_one_month_ippa_uid = parent_one_month_eac.parent_one_month_eac_uid
INNER JOIN parent_one_month_eval ON parent_one_month_eac.parent_one_month_eac_uid = parent_one_month_eval.parent_one_month_eval_uid
INNER JOIN child_three_month_survey ON parent_one_month_eval.parent_one_month_eval_uid = child_three_month_survey.child_three_month_survey_uid
INNER JOIN child_three_month_scas ON child_three_month_survey.child_three_month_survey_uid = child_three_month_scas.child_three_month_scas_uid
INNER JOIN child_three_month_smqf ON child_three_month_scas.child_three_month_scas_uid = child_three_month_smqf.child_three_month_smqf_uid
INNER JOIN parent_three_month_survey ON child_three_month_smqf.child_three_month_smqf_uid = parent_three_month_survey.parent_three_month_survey_uid
INNER JOIN parent_three_month_scas ON parent_three_month_survey.parent_three_month_survey_uid = child_three_month_smqf.child_three_month_smqf_uid
INNER JOIN parent_three_month_smqf ON parent_three_month_scas.parent_three_month_scas_uid = parent_three_month_smqf.parent_three_month_smqf_uid
INNER JOIN parent_three_month_eval ON parent_three_month_smqf.parent_three_month_smqf_uid = parent_three_month_eval.parent_three_month_eval_uid
INNER JOIN cron ON parent_three_month_eval.parent_three_month_eval_uid = cron.cron_uid");

?>

Answer 1

在我看来，您缺少一个 where 子句。实际上，它正在获取所有数据。从上面的评论来看，您似乎内存不足。你不应该为一个用户过滤它吗？或者尝试限制为 1 进行测试 - 只是为了证明查询本身没问题。