jd22 Posted September 18, 2011 Share Posted September 18, 2011 Hello I am trying to do something but seems i stuck. I have a mysql query but i guess it need optimization. I really appreciate for any help Regards $QUERY = " SELECT * FROM student s LEFT JOIN address a ON s.Postcode = a.Postcode"; $res = mysql_query($QUERY, MyConn()) or die(mysql_error()); while($row = mysql_fetch_array($res)) { $SQL2 = "SELECT * FROM fenrolment f LEFT JOIN Course c ON f.courseid = c.courseid WHERE f.studentid = '" .$row['StudentID']. "'"; $SQL3 = "SELECT * FROM enroll e LEFT JOIN SCourse s ON e.SCourseID = s.SCourseID WHERE e.studentid = '" .$row['StudentID']. "'"; echo $SQL2 ." < -- >". $SQL3 ."<BR>"; // Debug Purpose only } Quote Link to comment https://forums.phpfreaks.com/topic/247363-query-optimization/ Share on other sites More sharing options...
MasterACE14 Posted September 19, 2011 Share Posted September 19, 2011 I'm assuming what you currently has works? As far as optimisations are concerned, you should specify the fields being selected, instead of using an asterisk to select everything. Quote Link to comment https://forums.phpfreaks.com/topic/247363-query-optimization/#findComment-1270548 Share on other sites More sharing options...
jd22 Posted September 19, 2011 Author Share Posted September 19, 2011 Dear MasterACE14 Thanks for reply I need all fields. However as you can see i am getting students name and then try to find which course they are taking in a loop. I want to do that with a single query. Also another question is: with that query i am taking everything. But some students are taking 3 or 4 classes and that query brings me same student 4 times (with different classes) How can i distinct only one class if a student takes 4 classes. I hope i made myself clear... Regards Quote Link to comment https://forums.phpfreaks.com/topic/247363-query-optimization/#findComment-1270555 Share on other sites More sharing options...
fenway Posted September 19, 2011 Share Posted September 19, 2011 It's not optimized because you're not using JOINs. Quote Link to comment https://forums.phpfreaks.com/topic/247363-query-optimization/#findComment-1270794 Share on other sites More sharing options...
jd22 Posted September 19, 2011 Author Share Posted September 19, 2011 It's not optimized because you're not using JOINs. Dear Fenway That is the point. how can i merge those 3 queries into one. and use distinct for any of table in those queries but student table. Regards Quote Link to comment https://forums.phpfreaks.com/topic/247363-query-optimization/#findComment-1270823 Share on other sites More sharing options...
fenway Posted September 20, 2011 Share Posted September 20, 2011 Explain that distinct requirement again...? Quote Link to comment https://forums.phpfreaks.com/topic/247363-query-optimization/#findComment-1271073 Share on other sites More sharing options...
sniperscope Posted September 21, 2011 Share Posted September 21, 2011 Explain that distinct requirement again...? Dear Fenway Thank you for your interest. After Googling for a while i came up with this query $QUERY = " SELECT * FROM `sdb`.`Student` s INNER JOIN addr a ON s.zipcode = a.zipcode INNER JOIN enrol en ON s.SID = en.SID INNER JOIN SCourse sc ON en.SCourseID = sc.SCourseID INNER JOIN course c ON sc.CourseID = c.CourseID INNER JOIN fenrol fe ON c.CourseID = fe.CourseID WHERE s.SName = '$SName' AND c.CourseID='$CourseID' AND sc.WeekDay='$WeekDay'"; My student table has one to many relation with course table Some student can get lesson twice or trice in a week and if i run query above then i got 3 records(if student takes tree course in a week) at same name, same address same age same group How can i distinct course (i just want to take only one course name. Student may take only one course in a week or takes 100 course in a week it doesn't matter) Lord... i am almost forgetting English. If my English hard to understand please tell me. Regards Quote Link to comment https://forums.phpfreaks.com/topic/247363-query-optimization/#findComment-1271324 Share on other sites More sharing options...
kickstart Posted September 21, 2011 Share Posted September 21, 2011 Hi To limit it to a single row you need to chose a particular course for each row (possibly the MAX course) and use a suitable aggregate function and GROUP BY. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/247363-query-optimization/#findComment-1271328 Share on other sites More sharing options...
sniperscope Posted September 21, 2011 Share Posted September 21, 2011 Hi To limit it to a single row you need to chose a particular course for each row (possibly the MAX course) and use a suitable aggregate function and GROUP BY. All the best Keith Dear Keith If you don't mind may i have an example Please Quote Link to comment https://forums.phpfreaks.com/topic/247363-query-optimization/#findComment-1271329 Share on other sites More sharing options...
kickstart Posted September 21, 2011 Share Posted September 21, 2011 Hi Not sure of all the field names (and depending on exactly what you want back there may well be a better way to do this), but as an example something like this should do it. SELECT * FROM `sdb`.`Student` s INNER JOIN addr a ON s.zipcode = a.zipcode INNER JOIN (SELECT SID, MAX(SCourseID) AS MaxCourseID FROM enrol GROUP BY SID) Deriv1 ON Deriv1.SID = s.SID INNER JOIN enrol en ON Deriv1.SID = en.SID AND Deriv1.MaxCourseID = en.sCourseID INNER JOIN SCourse sc ON en.SCourseID = sc.SCourseID INNER JOIN course c ON sc.CourseID = c.CourseID INNER JOIN fenrol fe ON c.CourseID = fe.CourseID WHERE s.SName = '$SName' AND c.CourseID='$CourseID' AND sc.WeekDay='$WeekDay'"; By the way, generally I would say do not use SELECT * All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/247363-query-optimization/#findComment-1271426 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.