Is there a way to optimize this using indexes?

I’m just now learning indexes, and I was wondering if it’s possible to optimize this code using them. Currently this code runs in 1.4 seconds.

SELECT
    C.yearID as year,
    name as teamName,
    C.lgID as league,
    D.cnt as totalBatters,
    C.cnt as aboveAverageBatters
FROM
    (SELECT 
        count(masterID) as cnt, A.yearID, A.teamID, A.lgID
    FROM
        (select 
        masterID,
            teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    GROUP BY teamID , yearID , lgID, masterid
    HAVING sum(AB) <> 0) B, (select 
        teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    WHERE ab is not null
    GROUP BY teamID , yearID , lgID
    HAVING sum(AB) <> 0) A
    WHERE
        A.avg >= B.avg AND A.teamID = B.teamID
            AND A.yearID = B.yearID
            AND A.lgID = B.lgID
    GROUP BY A.teamID , A.yearID , A.lgID) C,
    (SELECT 
        count(masterID) as cnt, yearID, teamID, lgID
    FROM
        batting
    GROUP BY yearID , teamID , lgID) D, 
    teams
WHERE
    C.cnt / D.cnt >= 0.75
        AND C.yearID = D.yearID
        AND C.teamID = D.teamID
        AND C.lgID = D.lgID
        AND teams.yearID = C.yearID
        AND teams.lgID = C.lgID
        AND teams.teamID = C.teamID

I’ve tried creating indexes for teamid, yearid, lgid, and ab, but performance is the same.

Please help!

Generally indexes will stop working when you run “top” select over a nested select, like:

(select 
        teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    WHERE ab is not null
    GROUP BY teamID , yearID , lgID
    HAVING sum(AB) <> 0) A
    WHERE
        A.avg >= B.avg AND A.teamID = B.teamID
            AND A.yearID = B.yearID
            AND A.lgID = B.lgID
    GROUP BY A.teamID , A.yearID , A.lgID) C

OR

   (SELECT 
        count(masterID) as cnt, yearID, teamID, lgID
   FROM
        batting
   GROUP BY yearID , teamID , lgID) D

You define derived tables C (as well as D etc.). Also your derived tables have GROUP and FUNCTION inside.

When you make the comparison:

WHERE
    C.cnt / D.cnt >= 0.75
        AND C.yearID = D.yearID
        AND C.teamID = D.teamID
        AND C.lgID = D.lgID
        AND teams.yearID = C.yearID
        AND teams.lgID = C.lgID
        AND teams.teamID = C.teamID

it runs over new “tables” and these new tables do not have indexes.