Query with JOIN slow

0
votes

why my query take about 2 minutes to process?

i need to take attribute from 8 table.

how can i join those table and make it a fast query?

the reason why i want to join those table is bacause i want to fetchAll(PDO::FETCH_ASSOC) from user input progCode

this is my query :

"SELECT DISTINCT a.`ProgCode`, a.`Program` 
   FROM (select `i`.`name` AS `LC`,`f`.`name` AS `Intake`,`a`.`student_id` 
   AS `student_id`,`b`.`matricNo` 
   AS `matricNo`,`b`.`name` 
   AS `Nama`,`a`.`sem_id` 
   AS `sem_id`,`c`.`name` 
   AS `Sessi`,`e`.`code` 
   AS `ProgCode`,`e`.`name` 
   AS `Program`,`a`.`sub_id` 
   AS `sub_id`,`d`.`code` 
   AS `SubCode`,`d`.`name` 
   AS `Subject`,`a`.`grade` 
   AS `grade`,`h`.`credit` 
   AS `CurrentCreditHour`,`g`.`totalcredit` 
   AS `TotalCreditHour`,`g`.`gpa` 
   AS `GPA`,`g`.`cgpa` 
   AS `CGPA` from ((((((((`admin_sub_mark` `a` join `enrl_student` `b`) 
     join `struc_session` `c`) 
     join `struc_session` `f`) 
     join `struc_subject` `d`) 
     join `struc_program` `e`) 
     join `admin_sem_wise_cgpa` `g`) 
     join `admin_sem_wise_gpa` `h`) 
     join `struc_learningcentre` `i`) 
  where ((`b`.`id` = `a`.`student_id`) 
  and (`d`.`id` = `a`.`sub_id`) 
  and (`c`.`id` = `a`.`sem_id`) 
  and (`e`.`id` = `b`.`program_id`) 
  and (`f`.`id` = `b`.`intake_id`) 
  and (`i`.`id` = `b`.`learningCenter_id`) 
  and (`a`.`student_id` = `g`.`student_id`) 
  and (`a`.`sem_id` = `g`.`sem_id`) 
  and (`h`.`student_id` = `g`.`student_id`) 
  and (`h`.`sem_id` = `g`.`sem_id`)) order by `b`.`name`) a  
  ORDER BY `Program` ASC"

this is my explain query:

id| select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

1 | SIMPLE | g | ALL | NULL |NULL | NULL | NULL| 6049 | Using temporary; Using filesort

1 | SIMPLE | h | ALL | NULL | NULL | NULL | NULL | 6055 | Using where |

1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 257 | educate_aeu2.g.sem_id 1

1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 257 | educate_aeu2.h.student_id 1 Using where

1 | SIMPLE | f | eq_ref |PRIMARY | PRIMARY | 257 | educate_aeu2.b.intake_id 1

1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 257 | educate_aeu2.b.program_id 1

1 | SIMPLE | i | eq_ref | PRIMARY | PRIMARY | 257 | educate_aeu2.b.learningCenter_id 1

1 | SIMPLE | a |ALL | NULL | NULL | NULL | NULL | 17077 | Using where

1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 257 | educate_aeu2.a.sub_id 1

1
phpmysql
Start by doing an EXPLAIN on the query and look at what indexes are/aren't being used – Mark Baker
use index for appropriate fields... – user1844933
MySQL can EXPLAIN and EXPLAIN EXTENDED your query better than we can, because MySQL already knows what the schema's look like – Elias Van Ootegem
If you need data from 8 tables with 1 query, you should take a second look at your database. Are you sure you divided the data into a good structure of tables? – Wesley De Keirsmaeker
yes.. i can create a view with that query but the view is also slow.. so i decided to put that on php code. – user3269893

1 Answers

0
votes

You are retreiving a lot of fields in the inner select and doing a sort, then ignoring most of those fields and doing another sort. This will waste time

Quick clean up of the code gives:-

SELECT DISTINCT 
   e.code AS ProgCode,
   e.name AS Program
FROM admin_sub_mark a 
JOIN enrl_student b ON b.id = a.student_id
JOIN struc_session c ON c.id = a.sem_id
JOIN struc_session f ON f.id = b.intake_id
JOIN struc_subject d ON d.id = a.sub_id
JOIN struc_program e ON e.id = b.program_id
JOIN admin_sem_wise_cgpa g ON a.student_id = g.student_id AND a.sem_id = g.sem_id
JOIN admin_sem_wise_gpa h ON h.student_id = g.student_id AND h.sem_id = g.sem_id
JOIN struc_learningcentre i ON i.id = b.learningCenter_id
ORDER BY e.name ASC

Not sure whether you need to access all the tables.

Further what indexes do you have on the tables? If a query like this is running slowly then the culprit is normally the indexes but without knowing what they are (or better, the output from an EXPLAIN) we will struggle to provide any useful help