CODEWITHSUNDEEP
mysqlsqlquery-optimization
Yorushi
Yorushi

Reputation: 13

SQL Optimize Approach

I have a newbie question regarding optimizing this query

Assuming I have 3 tables. Student, Class and Group

Assuming I have trillions of data/records, what's the optimal way for this query? Should I just create an Indexes for each column I've used? Create a temporary table first instead of sub query? Thanks!


SELECT  Student_ID,
    BDate,
    SUM(Calc1 + Calc2 ) / 2 AS Calc4
        FROM (
            SELECT a.student_id AS Student_ID, a.birthday AS BDate, 
        SUM(a.test1 + a.test2 / a.test3) AS Calc1,
        SUM(a.test1 + a.test2 + a.test3) AS Calc2,
        AVG(b.test1 + b.test2 + b.test3)*2 AS Calc3,
             
            FROM Students a
            LEFT JOIN Group b on a.Name = b.Name
            LEFT JOIN Class c on  b.Name = c.Name
            WHERE a.Forte ('Math', 'PE')
            AND a.Hobby ('Eating', 'Video Games')
            GROUP BY a.Student_ID, a.birthday
                 ) subA
               GROUP BY Student_ID, BDate

Upvotes: 0

Views: 39

Answers (1)

Rick James
Rick James

Reputation: 142366

  • Get rid of the outer SELECT, just fold calc4 in.
  • Get rid of Calc3; you don't use it.
  • INDEX(Student_ID, birthday) on a

I would hope that b and c have PRIMARY KEY(name). But, with a million rows, there will be duplicates. So, I declare the question incomplete.

Upvotes: 1

Related Questions