The optimization process and recommendations:

1. Avoid Calling Functions With Indexed Columns (query line: 40): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `PilotStationDate` is indexed, the index won’t be used as it’s wrapped with the function `DATE_FORMAT`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
2. Avoid Calling Functions With Indexed Columns (query line: 43): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `PilotStationDate` is indexed, the index won’t be used as it’s wrapped with the function `DATE_FORMAT`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
3. Avoid Calling Functions With Indexed Columns (query line: 35): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `PilotStationDate` is indexed, the index won’t be used as it’s wrapped with the function `DATE_FORMAT`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
4. Avoid Subqueries (query line: 23): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
5. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.

The optimized query:

SELECT
        DISTINCT MONTH,
        CASE 
            WHEN Cabang.ID_Cabang = 'C001' THEN PWT END AS A,
CASE 
    WHEN Cabang.ID_Cabang = 'C002' THEN PWT END AS B,
CASE 
    WHEN Cabang.ID_Cabang = 'C003' THEN PWT END AS C,
CASE 
    WHEN Cabang.ID_Cabang = 'C004' THEN PWT END AS D,
CASE 
    WHEN Cabang.ID_Cabang = 'C005' THEN PWT END AS E 
FROM
`keyperformanceindicator` 
INNER JOIN
pilot 
    ON keyperformanceindicator.ID_Pilot = pilot.ID_Pilot 
INNER JOIN
cabang 
    ON keyperformanceindicator.ID_Cabang = cabang.ID_Cabang 
INNER JOIN
(
    SELECT
        DATE_FORMAT(PilotStationDate,
        '%m') AS MONTH,
        FORMAT(SUM(`PilotWaitingTime`) / COUNT('PilotStationDate'),
        3) AS PWT,
        COUNT('PilotStationDate') AS jumlah 
    FROM
        keyperformanceindicator 
    INNER JOIN
        pilot 
            ON keyperformanceindicator.ID_Pilot = pilot.ID_Pilot 
    GROUP BY
        DATE_FORMAT(PilotStationDate,
        '%m') 
    ORDER BY
        NULL
) x 
    ON DATE_FORMAT(PilotStationDate,
'%m') = x.Month 
GROUP BY
DATE_FORMAT(PilotStationDate,
'%m') 
ORDER BY
UNIX_TIMESTAMP(CONCAT_WS('',
PilotStationDate,
' ',
PilotStationTime)) ASC