Is this query optimized?

[zaw sze]

I’m not sure if the query I’m going to show you has already reached its optimal. I can’t seem to improve it further. Please help to see if it can be further enhanced.

It takes about 20 sec to run this query but returned only 58 rows of records.
I'm using MySQL v4.1.12.

The indexes are as below:
1) Aircraft on (ICAO_Code, Aircraft_Type)
2) Seat on (Flight_ID, Seat_Class_ID)
3) Usage_Type on (Usage_Type_Name)
4) Usages on (Stop_Timestamp)

Below is the EXPLAIN analysis:

mysql> EXPLAIN
-> select
->
-> case when Usage_Type_Name = 'AOD' then AOD.Album_Name
-> when Usage_Type_Name = 'Song' then Song.Song_Name
-> end as "Content Name",
-> count(Usages.Usage_ID) as "# Hits"
->
-> from Aircraft
-> join Flight on
-> Flight.Aircraft_ID = Aircraft.Aircraft_ID
->
-> join Seat_Class on
-> Seat_Class.Flight_ID = Flight.Flight_ID
->
-> join Seat on
-> (Seat.Flight_ID = Flight.Flight_ID
-> and
-> Seat.Seat_Class_ID = Seat_Class.Seat_Class_ID
-> )
->
-> join Usage_Type
->
-> join Usages on
-> (Usages.Usage_Type_ID = Usage_Type.Usage_Type_ID
-> and
-> Seat.Seat_ID = Usages.Seat_ID
-> )
->
-> left join AOD on
-> AOD.AOD_ID = Usages.Content_ID
->
-> join Song on
-> Song.Song_ID = Usages.Content_ID
->
-> where
-> Aircraft.ICAO_Code = 'XXX'
-> and
-> Usage_Type.Usage_Type_Name in ('AOD', 'Song')
-> and
-> Usages.Stop_Timestamp >= date_sub(curdate(), interval 30 day)
->
-> group by "Content Name";

+----+-------------+------------+--------+--------------------------------------------------+---------------------+---------+-------------------------------------------------------+------+-----------------------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------+--------+--------------------------------------------------+---------------------+---------+-------------------------------------------------------+------+-----------------------------------------------------------+

| 1 | SIMPLE | Aircraft | ref | PRIMARY,Aircraft_Index_1 | Aircraft_Index_1 | 11 | const | 27 | Using where; Using index; Using temporary; Using filesort |

| 1 | SIMPLE | Flight | ref | PRIMARY,Flight_FKIndex1 | Flight_FKIndex1 | 4 | PAXUS.Aircraft.Aircraft_ID | 80 | Using index |

| 1 | SIMPLE | Seat_Class | ref | PRIMARY,Seat_Class_FKIndex1 | Seat_Class_FKIndex1 | 4 | PAXUS.Flight.Flight_ID | 1 | Using index |

| 1 | SIMPLE | Seat | ref | PRIMARY,Seat_FKIndex1,Seat_FKIndex2,Seat_Index_1 | Seat_Index_1 | 8 | PAXUS.Flight.Flight_ID,PAXUS.Seat_Class.Seat_Class_ID | 56 | Using index |

| 1 | SIMPLE | Usages | ref | Usages_FKIndex2,Usages_FKIndex3,Usages_Index_3 | Usages_FKIndex2 | 4 | PAXUS.Seat.Seat_ID | 3 | Using where |

| 1 | SIMPLE | Usage_Type | eq_ref | PRIMARY,Usage_Type_Index_1 | PRIMARY | 4 | PAXUS.Usages.Usage_Type_ID | 1 | Using where |

| 1 | SIMPLE | AOD | eq_ref | PRIMARY | PRIMARY | 4 | PAXUS.Usages.Content_ID | 1 | |

| 1 | SIMPLE | Song | eq_ref | PRIMARY | PRIMARY | 4 | PAXUS.Usages.Content_ID | 1 | |

+----+-------------+------------+--------+--------------------------------------------------+---------------------+---------+-------------------------------------------------------+------+-----------------------------------------------------------+

8 rows in set (0.00 sec)

The query is already using indexes. What more can be done to optimize it?
Thanks for giving some feedback.