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

CodePudding user response：

• 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.