MySQL Forums
Forum List  »  Optimizer & Parser

Is this query optimized?
Posted by: zaw sze
Date: February 09, 2006 09:44PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Is this query optimized?
2238
February 09, 2006 09:44PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.