Page 1 of 1

slow query

Posted: Wed Apr 26, 2006 8:20 am
by gurjit
hi,

i have written a query like this in php, it takes a long time to load, considering there are only 150 students

any idea why?

Code: Select all

<?php
$family_list_all = ARRAY(); 

$sql_schools_to_doP = "select scid,tbl_student.frn_familyid from tbl_school,tbl_student where tbl_student.frn_scid = tbl_school.scid and tbl_school.frn_lid = 88 and sc_status = 1 and sc_type = 'school' and ((tbl_student.stu_status = 1) or (tbl_student.stu_status = 3 and tbl_student.stu_tid != 0)) order by stu_dob asc";
$mysql_result_schools_to_doP = mysql_query($sql_schools_to_doP);
$num_rows_schools_to_doP = mysql_num_rows($mysql_result_schools_to_doP);

while ($row = mysql_fetch_array($mysql_result_schools_to_doP))
			{
			
			
			 	$scidp = $row["scid"];
				$frn_familyid = $row["frn_familyid"];


$sql_siblingP = "select tbl_student.stuid as older_stuid,stu_fname1,stu_lname from tbl_student where frn_familyid = $frn_familyid and ((tbl_student.stu_status = 1) or (tbl_student.stu_status = 3 and tbl_student.stu_tid != 0)) order by tbl_student.stu_dob asc";
$mysql_result_siblingP = mysql_query($sql_siblingP);
$num_rows_siblingP = mysql_num_rows($mysql_result_siblingP);
$get_siblingP = mysql_fetch_assoc($mysql_result_siblingP);


array_push($family_list_all, $frn_familyid);
}
}
?>

Posted: Wed Apr 26, 2006 10:09 am
by feyd
You perform a join for the first query, then perform another query based on every record from the first query.

Posted: Fri Apr 28, 2006 9:58 am
by gurjit
I have created a join as you suggest, the first query has the join between tbl_student and tbl_school and the second query is looping tbl_student again the find the siblings with the $familyid, which comes from the first query

Posted: Fri Apr 28, 2006 10:05 am
by RobertGonzalez
Why are you nesting your queries? You can either write one enormous joined query selecting what is needed from the tables in one query, or you can use the queries and match them up code side. Doing it the way you are doing it is making your script slow because you are running a query for each and every record returned from within the first query.

Posted: Fri Apr 28, 2006 10:57 am
by gurjit
can you please give me some guidance how I would do code side or query joining to achieve this......

I can use a left joint query but how can I find out the siblings names or number of siblings without looping the way I do now?

Also some students do not have siblings, so the left join query will not work

I need to find the oldest sibling and the names of siblings and the number of siblings.... how can I achieve this?

Posted: Fri Apr 28, 2006 12:24 pm
by RobertGonzalez
Post your table structures and what exactly you want to see.

Posted: Thu May 04, 2006 3:32 am
by gurjit
tbl_student
stuid, int, auto
stu_fname, varchar(50)
stu_lname, varchar(50)
frn_scid, int
frn_familyid, int
stu_dob, date


tbl_family
familyid, int, auto
family_name, varchar (50)

tbl_school
scid, int, auto
sc_name, varchar(50)

In the student table (tbl_student):
1, Joe, Bloggs, 88, 60, 1977-12-15
2, James, Bloggs, 88, 60, 1980-12-16
3, Tom, Bloggs, 92, 60, 1990-11-02
4, Harold, Bishop, 88, 61, 1988-08-06

In the Family table (tbl_family):
60, Bloggs
61, Bishop

In the school table (tbl_school):
88, Harrow 1
92, Harrow 2


What I want to display is the oldest student in the family and what school they goto, so my results will pull out from the tbl_student table, records:
1, Joe, Bloggs, 88, 60, 1977-12-15
4, Harold, Bishop, 88, 61, 1988-08-06


How can I acheive this?