MySQL JOIN LIMIT DYNAMIC QUERY OPTIMIZATION

Question

Here the schema : -

Student_Details
ID | Student_id | Name          | Value
1  | 2          | City          | NewYork
2  | 2          | Height        | 5'11'
3  | 2          | Class         | B
4  | 2          | RollNo        | 265454
5  | 2          | Credit        | 800
6  | 3          | City          | Manila
7  | 3          | Height        | 5'10'
8  | 3          | Type          | International
9  | 3          | RollNo        | 2653
10 | 1          | Address       | Main Street
11 | 1          | Height        | 5'9'
12 | 1          | TST           | 21.54
13 | 1          | CCA           | 242
13 | 1          | SEX           | Male

Students 
ID | Name   | Age | AddedOn
1  | Alpha  | 27  | 20 Jan 2019
2  | Beta   | 15  | 19 Jan 2019
3  | Gamma  | 18  | 20 Jan 2019 

Student_Dep_Map
ID |DEP_ID | Student_id
 1 | 4 | 2
 2 | 2 | 1

Department 
ID| Name 
1 | Science
2 | Physics
3 | Chemistry 
4 | Psychology

So Search revolves around Students , we need to search Students with various search criteria such as 'City' ,'Height' Or 'TST' ,Student in which Departments , Added On , Age ,etc.., basically all these tables. A dynamic search . Also output search should also contains some of the Fixed details from each tables. So I made this query with pivot.

select distinct s.*
FROM  students s
LEFT JOIN ( SELECT s.ID as s_id, group_concat(distinct d.name SEPARATOR ', ') as ASSIGNED_DEPT FROM students s   
          JOIN  Student_Dep_Map sdp JOIN  Department d on sdp.DEP_ID = d.ID ON sdp.Student_id=s.id  GROUP BY s.id  ) aa  ON aa.s_id = s.ID 
LEFT JOIN ( SELECT DISTINCT  student_id, 
            MAX(if(Name = 'TST', value, null)) as TST,   
                        MAX(if(Name = 'Height', value, null)) as HEIGHT, 
            MAX(if(Name = 'CITY', value, null)) as CITY, 
            MAX(if(Name = 'SEX',  value, null)) as SEX,
            MAX(if(Name = 'RollNo',  value, null)) as ROLLNO 
            FROM student_detials tip  GROUP BY  student_id ) sd ON sd.student_id = s.ID
WHERE 1=1 AND SD.SEX = 'Male' LIMIT 0,100 ;

AND criteria is added dynamically through query builder.

Challenge is ! This Query is too slow, as records in tables increased , query became slow drastically . I did use explain to understand the query plan . if I leave indexing & just talk about optimizing query plan. I noticed when I used pivot even though the search criteria is from main table student still query plan goes through almost all records while pivoting in student details. is there any way I can limit joins until 100 records of Students records are found. One way is to write sub query for each table to LIMIT results but is there any better approach handle this ? I need to Limit joins not just results after joins , so Query results in faster fetch.

Note : Sorry I cannot post real tables , its just mock data.

Answer 1

I would recommend using exists instead of aggregating:

SELECT s.*,
       (SELECT GROUP_CONCAT(d.name SEPARATOR ', ')
        FROM Student_Dep_Map sdp JOIN
             Department d 
             ON sdp.DEP_ID = d.ID 
        WHERE sdp.Student_id = s.id
       ) as ASSIGNED_DEPT
FROM students s 
WHERE EXISTS (SELECT 1
              FROM student_details sd
              WHERE sd.student_id = s.id AND
                    sd.Name = 'SEX' AND
                    sd.Value = 'Male'
             )
LIMIT 0, 100 ;

For performance, you want indexes on:

• Student_Dep_Map(student_id, dep_id)
• Departments(id, name) (probably exists because id should be the primary key)
• student_details(student_id, name, value).

To add more conditions, add more EXISTS subqueries.