1

I want to select data from multiple tables but as the data gets larger my system become slow when fetching from MySQL database.

How can I make this query more efficient.

SELECT s.id,s.regiNo, 
       s.firstName as fname, 
       s.middleName as mname, 
       s.lastName as lname, 
       s.gender, s.class_group, c.subjects,
       e.mid_term, e.count_assessment, e.examid, 
       e.scored, e.internaltype, e.Class, 
       e.Year,e.total_score,e.sub_highest,e.sub_lowest,
       e.first_term,e.second_term,e.third_term,e.avg_score 
FROM student s 
inner join exam e on s.regiNo = e.Roll_Number 
inner join subjects c on e.sub_id = c.subect_code  
group by s.regiNo, s.firstName, s.middleName, s.lastName, 
         s.gender, s.class_group, c.subjects,
         e.mid_term, e.count_assessment, e.examid, e.scored, e.internaltype, 
         e.Class, e.Year,e.total_score,e.sub_highest,e.sub_lowest,
         e.first_term,e.second_term,e.third_term,e.avg_score,s.id
Hichem BOUSSETTA
  • 1,791
  • 1
  • 21
  • 27
Olajumoke Ademilade
  • 191
  • 1
  • 14
  • You'll probably get more help if you format the query so it is easier to read; and provide more information about the data you are querying (number of rows, table definitions, indexes, etc...). – Uueerdo Apr 05 '19 at 17:17
  • 2
    Why do you need a group by at all? I see no aggregation. I'm assuming you're joinining on PK to FK relationships which would be indexed so unless you add a where clause to limit the data returned, it's going to get slower as you add more data. – xQbert Apr 05 '19 at 17:21
  • It's really simple. If you don't have an aggregating function, don't include a GROUP BY clause. Never. Not ever. – Strawberry Apr 05 '19 at 20:28

1 Answers1

1

I see you have ID column, if that is your PK then you probably need some indexes.

Try creating index on JOINING columns.. such as regiNo, Roll_Number on

Ganesh Chandrasekaran
  • 1,578
  • 12
  • 17