### Question :

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!

### Answer :

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.