CODEWITHSUNDEEP
phpmysqlinner-join
thewomb
thewomb

Reputation: 71

very Slow Mysql INNER JOIN

This query works fine on my local dev machine, but takes ages on the remote server and seems to time out the server with an error 500, is there a better way to write this select statement?, any advice would be greatly received.

thanx

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");

?>

Upvotes: 2

Views: 207

Answers (3)

thewomb
thewomb

Reputation: 71

It would appear that the hosting company has been having server issues. hence the error 500 and timeouts,

thank you all for your help and insights.

J

Upvotes: 1

Rohit Gupta
Rohit Gupta

Reputation: 4191

It seems to me that you are missing a where clause. As it is, it is fetching all the data. And from comments above it seems you are running out of memory. Shouldn't you be filtering it for one user ? Or try a limit of 1 for testing - just to prove that query is okay in itself.

Upvotes: 0

BaBL86
BaBL86

Reputation: 2622

It's a very bad query, of course, but 500 error is not an TimeOut error. 500 - Internal Server Error, mean your script hangs after memory limit or segmentation fault.

Look at the server php error log for more information.

Upvotes: 0

Related Questions